Error with join 3 \IPS\Db\Select into a subquery

[BomAle]

I having issue when try to join 3+ \IPS\Db\Select to a subquery...

$join[] = [ \IPS\Db::i()->select( ... ), \IPS\Poll\Vote::$databaseTable .'.'. \IPS\Poll\Vote::$databasePrefix . 'poll=core_polls.pid'] ;
$join[] = array(\IPS\Db::i()->select( ... ), $class::$databaseTable . '.' . $class::$databasePrefix . $class::$databaseColumnMap['poll'] . '=core_polls.pid');
$join[] = array(\IPS\Db::i()->select( ... ), $class::$databaseTable . '.' . $class::$databasePrefix . $class::$databaseColumnMap['poll'] . '=core_polls.pid');
$subquery = \IPS\Db::i()->select( $select, 'core_polls',$where);
foreach ( $join as $j )
{
	$subquery->join( $j[0], $j[1]);
}
$query = \IPS\Db::i()->select( '*', $subquery, $wherealias, $sortBy, [ $offset, $this->limit ], NULL, NULL, \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS);

the result expected must be:

 

SELECT SQL_CALC_FOUND_ROWS *
FROM (SELECT
        core_polls.*,
        voted,
        .... AS item_class,
        .... AS item_id,
        .... AS item_perm
      FROM `core_polls`
        LEFT JOIN (SELECT
                     .....
                   FROM `forums_topics`
                     LEFT JOIN `core_permission_index` ON core_permission_index.app = 'forums' AND
                                                          core_permission_index.perm_type = 'forum' AND
                                                          core_permission_index.perm_type_id =
                                                          forums_topics.forum_id) AS `forums_topics`
          ON forums_topics.poll_state = core_polls.pid
        LEFT JOIN (SELECT
                     ....
                   FROM `cms_custom_database_1`
                     LEFT JOIN `core_permission_index` ON core_permission_index.app = 'cms' AND
                                                          core_permission_index.perm_type = 'categories'
                                                          AND core_permission_index.perm_type_id =
                                                              cms_custom_database_1.category_id) AS `cms_custom_database_1`
          ON cms_custom_database_1.poll_state = core_polls.pid
        LEFT JOIN (SELECT
                     COUNT(*) AS voted,
                     poll
                   FROM `core_voters`
                   WHERE member_id = 1
                   GROUP BY `member_id`, `poll`) AS `core_voters`
          ON core_voters.poll = core_polls.pid) AS `618a598a42b4749ec66919735ac00416`
WHERE
  poll_closed = FALSE AND poll_question LIKE '%%' AND (item_class IN ('IPS\\forums\\Topic', 'IPS\\cms\\Records1')) AND
  voted IS NOT NULL AND item_perm != 'NO' AND CHAR_LENGTH(item_id) >= 1 AND CHAR_LENGTH(item_class) > 1
ORDER BY start_date DESC
LIMIT 0, 4

actual result:

 

SELECT SQL_CALC_FOUND_ROWS *
FROM (SELECT
        core_polls.*,
        voted,
        ... AS item_class,
        ... AS item_id,
        ... AS item_perm
      FROM `core_polls`
        LEFT JOIN (SELECT
                     COUNT(*) AS voted,
                     poll
                   FROM `core_voters`
                     LEFT JOIN (SELECT
                                  ...
                                FROM `forums_topics`
                                  LEFT JOIN `core_permission_index` ON core_permission_index.app = 'forums' AND
                                                                       core_permission_index.perm_type = 'forum' AND
                                                                       core_permission_index.perm_type_id =
                                                                       forums_topics.forum_id) AS `forums_topics`
                       ON forums_topics.poll_state = core_polls.pid
                     LEFT JOIN (SELECT
                                  ...
                                FROM `cms_custom_database_1`
                                  LEFT JOIN `core_permission_index` ON core_permission_index.app = 'cms' AND
                                                                       core_permission_index.perm_type = 'categories'
                                                                       AND core_permission_index.perm_type_id =
                                                                           cms_custom_database_1.category_id) AS `cms_custom_database_1`
                       ON cms_custom_database_1.poll_state = core_polls.pid
                   WHERE member_id = 1
                   GROUP BY `member_id`, `poll`) AS `core_voters`
          ON core_voters.poll = core_polls.pid) AS `618a598a42b4749ec66919735ac00416`
WHERE
  poll_closed = FALSE AND poll_question LIKE '%%' AND (item_class IN ('IPS\\forums\\Topic', 'IPS\\cms\\Records1')) AND
  voted IS NOT NULL AND item_perm != 'NO' AND CHAR_LENGTH(item_id) >= 1 AND CHAR_LENGTH(item_class) > 1
ORDER BY start_date DESC
LIMIT 0, 4

Anyone could help me to make this code work?

EDIT: i remember @Colonel_mortis on this bug it still seems suspicious ( See WHERE member_id = 1.. and this regex (WHERE|GROUP BY|HAVING|LIMIT|ORDER BY|$) on \IPS\Db\Select::join() )

@Andy Millne this topic is refered to this bug

[BomAle]

I have solved with:

foreach ( array_reverse($join) as $j )

but is not the solution

[BomAle]

@bfarber

@Mark

[bfarber]

Can you create an example we can reproduce with using core tables out of the software? i.e. a little standalone script just so we can isolate and see the error?

 

<?php

require 'init.php';
\IPS\Dispatcher\External::i();

// Example DB select object with table joins go here

 

[BomAle]

<?php

require 'init.php';
\IPS\Dispatcher\External::i();

$subquery = \IPS\Db::i()->select( '*, voted, last_vote', 'core_polls');
$subquery->join( \IPS\Db::i()->select( 'COUNT(*) as voted, poll', \IPS\Poll\Vote::$databaseTable, array("member_id=?",\IPS\Member::loggedIn()->member_id), NULL,NULL,array('member_id', 'poll'),NULL,4), \IPS\Poll\Vote::$databaseTable .'.'. \IPS\Poll\Vote::$databasePrefix . 'poll=core_polls.pid');
$subquery->join( \IPS\Db::i()->select( 'MAX(vote_date) as last_vote, poll', [\IPS\Poll\Vote::$databaseTable,'copy_'.\IPS\Poll\Vote::$databaseTable], NULL, NULL,NULL,array('poll'),NULL,4), 'copy_'.\IPS\Poll\Vote::$databaseTable .'.'. \IPS\Poll\Vote::$databasePrefix . 'poll=core_polls.pid' );
print_r($subquery->query);exit;

Try also replace: array("member_id=?",\IPS\Member::loggedIn()->member_id) with: NULL ... and see that join is appended to group clause inside the first join and not into main query.

I think is this code the cause

$this->query = preg_replace( '/(WHERE|GROUP BY|HAVING|LIMIT|ORDER BY|$)/', $query . ' $1', $this->query, 1 );

[bfarber]

Thanks. I've raised the bug report internally, and we will prioritize it and investigate it for a future release.

[BomAle]

thanks bfarber for support

[BomAle]

This will fix for 4.2 @bfarber ?

[bfarber]

Yes

[BomAle]

thanks you i will test ?

[BomAle]

Anyone with 4.2 beta could test this?

 

[BomAle]

Confirm it work many thanks for this update