SQL optimization via Active Record

[eluser]taewoo[/eluser]
Hi everyone.

I am writing a standard user-to-user messaging application.
So every time someone sends or receives a message from another user, I'd have to query the USERS table to get their username, first name, last name, picture, etc.

To make it more efficient, I was thinking of doing something like (in pseudo-code):

Code:

select USER_NAME, FIRST_NAME, LAST_NAME, PICTURE from USERS where USERS.id IN ((select ID from senders) UNION (SELECT ID from recipients));

Is there a way to do this w/Active Record or do I need to spell it out w/raw SQL? Particularly the "in" comparison operator... is there a documentation for this?

[eluser]Derek Allard[/eluser]
There is a where_in() function in AR yes, but this particular query looks very (very) complex, and I think you're probably better off with just a

Code:

$this->db->query("select USER_NAME, FIRST_NAME, LAST_NAME, PICTURE from USERS where USERS.id IN ((select ID from senders) UNION (SELECT ID from recipients))");

[eluser]taewoo[/eluser]
Thanks Derek.
By the way, I saw your video tutorials. Pretty good stuff. Your video is probably the #1 reason why I switched from cakephp to CI

[eluser]xwero[/eluser]
I see you are using a subquery for the in values. It isn't supported in mysql before version 5 and i heard the performance is slow. Maybe you should do some benchmarking running two queries versus one. I would love to see some results (a quick search did't get any results).