How to "select DISTINCT" with metode Gdn::sql()

idlamalif

Hello, i ask again about database access.
I modified tag plugins to show trending tag based on latest tag add in discussion.

So, i use metode Gdn::sql() to show tag from database.

My question, how to use select DISTINCT.
I try this code but error:

->select('DISTINCT t.*')
->from('Tag t')
->join('TagDiscussion td', 't.TagID = td.TagID')
->orderBy('td.DateInserted', 'desc')
->limit(25)
->get();

River

here are a few examples that vanilla uses

->select('distinct c.InsertUserID', 'COUNT', 'Hits')

Gdn::SQL()->Select('c.DiscussionID','DISTINCT','NumDiscussions')

SQL->select('m.Name')->Distinct()->from('UserMeta m')->like('m.Name', 'Profile_%')->get();

$SQL->Distinct()
->join('Permission _p', '_p.JunctionID = '.$ForeignAlias.'.'.$ForeignColumn, 'inner')

another possibility is build your own query.

what is your error message?

R_J

In order to get a little more insight by yourself, try using getSelect() instead of get(). Instead of sending the sql command and returning the result, getSelect() will only show you the sql that the query builder has created.

$sql = Gdn::sql()->select('t.*')
    ->distinct(true)
    ->from('Tag t')
    ->join('TagDiscussion td', 't.TagID = td.TagID')
    ->orderBy('td.DateInserted', 'desc')
    ->limit(25)
    ->getSelect();
decho($sql);

You will see that your sql starts with "select DISTINCT as `t.*`" which explains why it throws an error.

While there are more ways to do so (see the above post), to me the most readable way to avoid that would be to use

->distinct(true)
->select('t.*')

idlamalif

Thanks for help., try it and work perfect.