Having count(*)

[ghost]

Hello, I've a issue with the having clause. I want to make a sql request like

SELECT * FROM User JOIN KeywordList ON userid=keyuserid  GROUP BY userid HAVING COUNT(userid)

How I can do that with your patio module !

[ghost]

I found it !!! I must use .groupAndCount() to do what I want but ... the result it's not what I want :( ...
first :

var dataset = db.fetch("SELECT * FROM t1 JOIN t2 ON t1k=t2k JOIN t3 ON t2k=t3k WHERE t3attr=? AND t2attr=? AND t1attr=?", parameters.A, parameters.B, parameters.B);

second :

dataset = dataset.groupAndCount('t1k').having({count : {gte : num}}).where('t3attr in {elements}', {elements: parameters.C});

when I use .sql after I've got this sql request which is not what I want

SELECT * FROM t1 JOIN t2 ON t1k=t2k JOIN t3 ON t2k=t3k WHERE t3attr='xxxx' AND t2attr='yyyy' AND t1attr='yyyy'

I don't have the COUNT(*) AS Count, the GROUP BY ... and the HAVING Count >= ...
Am I doing something wrong ... ?

Thank you

[doug-martin]

Hi, sorry took so long to get back to you.

The issue with providing the sql to begin with is that patio does not parse the sql into the fragments required to create the sql you are wanting. The way I would recommend doing it is to create the sql using the patio api or write the entire sql fragment within the db.fetch call.

To write the sql using the api you could do something like the following.

var dataset = db
    .from("t1")
    .join("t2", {t2k : sql.identifier("t1k")})
    .join("t3", {t3k : sql.identifier("t2k")})
    .where({t3attr : parameters.A, t2Attr : parameters.B, t1Attr : parameters.C }); 

var ds = dataset.groupAndCount('t1k')
    .having({count : {gte : 10}}) //or whatever number you want
    .where('t3attr in {elements}', {elements: parameters.C}); 

-Doug

[ghost]

Thank you !! It works nice now :)