By hyune on

Hi,

How to do a subselect with db_select ?

See this example:

SELECT `date` ,

  (SELECT COUNT( * )
  FROM `myTable` AS b
  WHERE b.action =0
  AND b.date = a.date
  ) AS inscription, 

FROM `myTable` AS a
GROUP BY `date`

How translate that with db_select and Drupal Db API ?

Thx,

⋅ Categories: Drupal 7.x

Comments

anil614sagar’s picture

anil614sagar commented

Check out http://api.drupal.org/api/drupal/includes%21database%21database.inc/func...

If you want to specifically use db_select then

1st Query

$count = SELECT COUNT( * )
FROM `myTable` AS b
WHERE b.action =0
AND b.date = a.date

2nd QUERY

$result = db_select('myTable', 'my')
->fields('date', $count)
->execute()
->fetchAssoc();

Cheers,
Anil Sagar,
Lead Drupal Developer,
Azri Soulutions,
http://azrisolutions.com/

hyune’s picture

hyune commented

I want to use db_select because i need to use drupal pager and tableSort (extend('PagerDefault')->limit(20)->extend('TableSort')). So i can't use db_query.

Your code don't work, but i found the response by myself:

$count = '(SELECT COUNT(*) FROM `myTable` AS b WHERE b.action =0 AND b.date = a.date)';

$query = db_select('myTable', 'a');
$query->addField('a', 'date', 'date');
$query->addExpression($count, 'inscription');
$query->groupBy('a.date');
amirimran’s picture

amirimran commented

Thanks hyune, this helped me as well :)