Lithium count with left join

Question

As part of a larger query with dynamically-generated elements, I need to do a left join and count. This is the query I need.

SELECT slug, name, count(client_tests.id) AS test_count
  FROM clients
  LEFT JOIN client_tests ON clients.id = client_tests.client_id
  GROUP BY clients.id;

I've tried to build a join query, but Lithium seems to be expecting the join to be part of a relationship (no relationships are defined), as shown below (ignoring the count for now).

$join = new Query(array(
  'source' => 'client_tests',
  'model' => '\app\models\ClientTest',
  'type' => 'LEFT',
  'constraint' => array('Client.id' => 'ClientTest.client_id'),
));
$clients = Client::all(array(
  'conditions' => $conditions,
  'group' => 'Client.id',
  'joins' => array($join)
));

This results in Notice: Undefined index: ClientTest in /usr/local/www/oars/libraries/lithium/data/collection/RecordSet.php on line 340, which seems to be relationship-related code.

If I do define a hasMany relationship between Client and ClientTest, which will handle building the left join for me, is there a way to get the Client fields and the count?

$clients = Client::all(array(
  'fields' => array('slug', 'name', 'count(ClientTest.test_id) as test_count'),
  'conditions' => $conditions,
  'group' => 'Client.id',
  'with' => 'ClientTest'
));

This results in ( ! ) Notice: Undefined index: count(ClientTest in /usr/local/www/oars/libraries/lithium/data/source/Database.php on line 650, so either it's not possible or I'm using the wrong syntax.

I could simply issue the query directly with Client::connection()->read($sql), but as there are dynamic elements to the query, I'd have to build the SQL anyway.

Is there a way to get the above methods to work, or should I go with manually building the SQL?

score 1 · Answer 1 · answered Jul 26 '12 at 14:15

If I'm understanding you correctly you already have the SQL query that returns the result set you're looking for but are having troubles with lithium. I would suggest trying this. If you create a view of the query that you need in SQL, you could then have lithium send its a simple request to the view instead. I have absolutely no experience with lithium but I hope that helps. Good luck.

score 0 · Answer 2 · answered May 05 '12 at 18:42

I must first admit that I am not familiar with Lithium. However, there are two ways to approach this in SQL, which you might as well try.

I am assuming that you are trying to get the total number of rows returned as an additional column on each row.

The first requires something called window functions (aka analytic functions in Oracle). The query would look like:

SELECT *, count(*) over (partition by clients.id) AS test_count
FROM clients LEFT JOIN client_tests
     ON clients.id = client_tests.client_id

The second approach is used in databases that do not support this. To handle it with one query, the following works:

with q as (SELECT c.*, <whatever columns you want from client_tests
           FROM clients LEFT JOIN
                client_tests
                ON clients.id = client_tests.client_id
          )
select q.*, qsum.test_count
from q join
     (select id, count(*) as test_count
      from q
      group by id
     ) qsum
     on q.id = qsum.id

Perhaps one of these will work in your case.

Writing the query isn't the problem, the best way to make it work in Lithium is specifically the question I've asked. — michaeltwofish, May 06 '12 at 08:28
Your original query does not make sense as SQL. It has "select *" with a group by. If you start with correct SQL query, perhaps the rest will fall in place. — Gordon Linoff, May 06 '12 at 12:27
I appreciate your help, and I was sloppy with the SQL, but the query isn't the issue. It's in the question to illustrate what I want to end up with but it's not used as input anywhere. — michaeltwofish, May 07 '12 at 02:38

score 0 · Answer 3 · answered May 05 '12 at 18:53

0

Your query seems weird. Try this:

SELECT slug, name, count(client_tests.id) AS test_count
FROM clients
LEFT JOIN client_tests ON clients.id = client_tests.client_id
GROUP BY clients.id;

answered May 05 '12 at 18:53

Bohemian

412,405
93
575
722

score 0 · Answer 4 · answered Aug 08 '12 at 13:56

0

Seems like you simply missTyped your table name,

'constraint' => array('Client.id' => 'ClientTest.client_id')

But you have :

LEFT JOIN client_tests ON clients.id = client_tests.client_id

Did you just made a mistake between client_tests and ClientTest?

answered Aug 08 '12 at 13:56

Ludovic Migneault

140
1
4
19

No, lithium aliases the tables automatically. – michaeltwofish Aug 26 '12 at 04:30

Lithium count with left join

4 Answers4