Help building a query using ActiveRecord

[eluser]stormbytes[/eluser]
What are the steps to follow to build the following query using ActiveRecord? (Read the manual...)

Code:

SELECT
  Concat(u.user_first_name, ' ', u.user_last_name) AS User,
  a.account_numb AS Account,
  s.state AS State
FROM
  Users AS u
INNER JOIN Accounts AS a
  ON u.user_id = a.user_id
INNER JOIN States AS s
  ON u.state_id = States.state_id

I'm having trouble figuring out how to piece this together using db->select() & db->join()
or is it that ActiveRecord is more suited for simple queries? Should I be doing this using db->query?

[eluser]cahva[/eluser]
This can be done with AR no problemo:

Code:

$q = $this->db->select("CONCAT(u.user_first_name,' ',u.user_last_name) User,a.account_numb Account,s.state State",FALSE)
    ->join('Accounts a','u.user_id=a.user_id','inner')
    ->join('States s','u.state_id=s.state_id','inner');

$q->get('Users u')->result();

I left out AS (the alias). It is not used by some DBMS(at the time) so I left it out couple of years ago.

[eluser]stormbytes[/eluser]
Hiya, cahva!

Well.. It took a couple of hours futzing around to adapt that to my code, but it worked like a charm!

Thanks so much for posting the tip.

[eluser]pbreit[/eluser]
Would "where" work ok instead of the two joins? Pros & cons?

[eluser]stormbytes[/eluser]
How's a 'where' replace the joins?

[eluser]pbreit[/eluser]
Isn't this the same query?

Code:

SELECT
  Concat(u.user_first_name, ' ', u.user_last_name) AS User,
  a.account_numb AS Account,
  s.state AS State
FROM
  Users AS u, Accounts AS a, States AS s
WHERE u.user_id = a.user_id
AND u.state_id = States.state_id

[eluser]techgnome[/eluser]
But for the fact that you didn't specify the join condition for the State table... in theory, yes... but not all DBMSs support that method of joining. And doesn't allow for left, right or outer joins, it's only effective with inner joins.

-tg

[eluser]pbreit[/eluser]
Isn't this the join condition for states?

Code:

AND u.state_id = States.state_id

[eluser]tonanbarbarian[/eluser]
most databases, mysql in particular are optimised to work better with joins rather than just using multiple from and where statements
joins is also the standard sql way of doing things so you should use them wherever possible.
you have more control over how the join works etc if you use a join rather than the from where

[eluser]marrysmile23[/eluser]
ActiveRecord provides an excellent way to manage your database entities. When it comes to the pursuit and acquisition of these entities in a flexible, ActiveRecord can do the trick in many cases, using different criteria.