AR and complex MySQL queries

Yii 1.1 General Discussion
1

I have the table for storing messages between 2 users groups (admins and users). Users stored in the different tables.

Messages table:

from_id <- id of the sender, it can be user or admin

to_id <- id of the receiver

rule <- rule, it can be one of ‘user-admin’, ‘user-user’, ‘admin-user’, ‘admin-admin’

So, for selecting sender name i am used the SQL query like this:




SELECT IF(messages.rule=0, <--- if admin

    (SELECT login FROM admins WHERE admins.id = messages.from),   <--- Subquery for admin name

    (SELECT login FROM users WHERE users.id = messages.from) <----- user name

) as name_from

How can i make this with AR ? :)

2

I think it’s better to use SQL with DAO for this one.

3

Maybe you could denormalize a little (define columns like from_user_id, to_user_id, from_admin_id, to_admin_id) and define relations with criteria.

just a thought …

4

Yes, i am thinking about it, but in this situation i need to join each table twice.

Thank you

5

Any ideas ?

I still can change ‘messages’ table structure…

6

Use

CActiveRecord#findAllBySql