1

Hi I'm trying to create a join query between two custom tables. One is publish_options with a pubid as the primary key. The other is publish_options_bundles with a referring field pubid to the first table.

The problem I'm having is that the results returned are as such:

Array
(
    [0] => stdClass Object
        (
            [__CLASS__] => stdClass
            [pubid] => 51
            [title] => Promote to blog
            [created] => 1549262900
            [modified] => 1549262900
            [bundle] => article
        )

    [1] => stdClass Object
        (
            [__CLASS__] => stdClass
            [pubid] => 51
            [title] => Promote to blog
            [created] => 1549262900
            [modified] => 1549262900
            [bundle] => gallery
        )

    [2] => stdClass Object
        (
            [__CLASS__] => stdClass
            [pubid] => 51
            [title] => Promote to blog
            [created] => 1549262900
            [modified] => 1549262900
            [bundle] => page
        )

)

I would like it to return like:

(
  [__CLASS__] => stdClass
  [pubid] => 51
  [title] => Promote to blog
  [created] => 1549262900
  [modified] => 1549262900
  [bundles] => [
    page,
    article,
    gallery,
  ]
)

The code I've come with is below.

$query = $this->connection->select('publishing_options', 'po')
      ->fields('po')
      ->condition('po.pubid', $id);
    $query->leftJoin('publishing_options_bundles', 'pob', 'po.pubid = pob.pubid');
    $query->fields('pob', ['bundle']);

    $result = $query->execute()->fetchAll();

Any help would be greatly appreciated as I am completely stumped now.

Thank you in advance.

8
  • Did you try distinct pub id? – Kevin Feb 4 '19 at 17:45
  • @Kevin, yeah, it's still giving me the same results. I'm not sure how to tell it to do it on pubid though as I see the param it wants is a boolean defaulted to TRUE. – Jorge Calderon Feb 4 '19 at 17:51
  • You wouldn't get the same results if you set distinct on the pubid. It will only return one result for each ID, and you haven't requested the other fields that are showing. In fact, the result you have shown us can't match the query you showed us, so it's hard to provide accurate help since your post does not sync up. – Jaypan Feb 5 '19 at 0:57
  • @Jaypan, that is actually the results it's giving me. using dsm() to output the results. I'm getting that with a fetchAll() call on the return. Could it be that Drupal is not outputting the correct results? Even if I try join() or rightJoing() I still get the same results. I can post a screenshot to show that it is outputting the above results if it helps. – Jorge Calderon Feb 5 '19 at 7:33
  • 1
    What SQL query are you trying to reproduce? To get the results you’ve mentioned you would have to be grouping and using aggregate functions, which you don’t appear to be. Drupal is just executing the query you’re telling it to, the chances of the results being “wrong” are slim to none. – Clive Feb 5 '19 at 7:59
1

I found the way to do what I wanted.

The modified query:

    $query = $this->connection->select('publishing_options', 'po')
      ->fields('po')
      ->condition('po.pubid', $id);
    $query->join('publishing_options_bundles', 'pob', 'po.pubid = pob.pubid');
    $query->addExpression('GROUP_CONCAT(DISTINCT pob.bundle)', 'bundles');
    $query->groupBy(['po.pubid']);
    $result = $query->execute()->fetch();

I added the groupBy() methond and the addExpression() method with a GROUP_CONCAT

The results:

stdClass Object
(
    [__CLASS__] => stdClass
    [pubid] => 51
    [title] => Promote to blog
    [created] => 1549262900
    [modified] => 1549262900
    [bundles] => article,gallery,page
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.