FuelPHP Forums

Jume

I have to achieve something like this

SELECT ...

FROM ...

LEFT JOIN (SELECT ... ) alias ON alias.id = id

How to have a subquery in join? I tried several things:

// not working

$subquery = \DB::select()....

$query = \DB::select()->from('table')->join($subquery, 'LEFT')

//not working

$query = \DB::select()->from('table')->join(\DB::expr('(SELCET FROM ....)'), 'LEFT')

Can you help?

Harro

Is not supported at the moment.

If you are on 1.9/develop, you can try to replace L#123 in /core/classes/database/query/builder/join.php by this:

        if ($this->_table instanceof \Database_Query_Builder_Select)
        {
            // Compile the subquery and add it
            $sql .= ' ('.$this->_table->compile().')';
        }
        else
        {
            // Quote the table name that is being joined
            $sql .= ' '.$db->quote_table($this->_table);
        }

and then your first example should work.

Let me know if it does, so I can commit it. I don't have time to test it myself atm.

Harro

Have you managed to test this solution yet?

atabak

hi, i use \DB::expr() in join for that query:
$a = \DB::select()
                ->from([\Acl\Model_User::table(), 'u'])
                ->join([\DB::expr('(SELECT id FROM acl_users_groups)'), 'g'], 'left')
                ->on('g.id', '=', 'u.group_id')
                ->execute();
query : SELECT * FROM "acl_users" AS "u" LEFT JOIN (SELECT id FROM acl_users_groups) AS "g" ON ("g"."id" = "u"."group_id")

Harro

Ah, but you added the brackets, and the TS didn't, so that is why it didn't work for you.

Good to know this is being used, because I've implemented DB::expr() explicitly, which would break your code because it would add brackets again. I'll take that into account.

Harro

https://github.com/fuel/core/commit/f1db7c9a8b27cd2afd6ceb223909610bfebf8902

Now all three options work: subqueries, and DB::expr() with and without brackets.

atabak

hi Harro , i add new line to join.php (https://github.com/fuel/core/commit/f1db7c9a8b27cd2afd6ceb223909610bfebf8902
) and work with subquery in join but not work with DB::expr() in this query :
->join([\DB::expr('SELECT * FROM acl_users_groups'), 'g'], 'left')
not add table alias (g) to query, Do you have any suggestions?

Harro

What is the exact SQL that is generated using that syntax?

atabak

sql = 'SELECT * FROM "acl_users" AS "u" LEFT JOIN (SELECT * FROM "acl_users_groups") AS "g" ON ("g"."id" = "u"."group_id")'

$a = \DB::select()
                ->from([\Acl\Model_User::table(), 'u'])
                ->join([\DB::expr('SELECT id FROM acl_users_groups'), 'g'], 'left')
                ->on('g.id', '=', 'u.group_id')
                ->execute();

atabak

add this lines before else (join.php L#132)

elseif (is_array($this->_table) && $this->_table[0] instanceof \Database_Expression)

{

$sql .= ' ('.trim($this->_table[0], ' ()').') AS '.$this->_table[1];

}

and work but not look good

Harro

That SQL is the same as you posted before, so what is the problem exactly?

I don't see anything wrong with

'SELECT * FROM "acl_users" AS "u" LEFT JOIN (SELECT * FROM "acl_users_groups") AS "g" ON ("g"."id" = "u"."group_id")'

$this->_table can't be an array, the Join class never had any support for that?

Harro

Ah, wait, the alias support is hidden in quote_table().

I think it's better to split tablename and alias in the constructor if an array is passed, and then handle those seperately when compiling.

This should do it: https://github.com/fuel/core/commit/ccd008e6051ce68f66846ecb28276ff645732123

atabak

thanks Harro,

i cant test it now.

but

$this->quote_identifier($this->_alias);

in L#155 may not work!

tomorrow i test it and send you result

Harro

Should be $db->, it is a typo, and just changed in the repo.

atabak

thanks,

how can i use DB in query like:

select

t0.id,

t0.name,

(select count(id) from table2 where somfield = t0.id) as cnt

from table1 as t0;

i use \DB::expr() in field name;

atabak

for query like :

$select_subquery = \DB::select(\DB::expr('count(id)'))->from(\Acl\Model_Group::table())->where('id', \DB::expr('u.group_id'));

$main_query = \DB::select('u.id', 'u.username', $select_subquery)

->from([\Acl\Model_User::table(), 'u'])

->execute();

(very bad sample i agree ;) )

i change select.php->compile function for my query :

//$query .= implode(', ', array_unique(array_map($quote_ident, $this->_select)));

$this->_select = array_map("unserialize", array_unique(array_map("serialize", $this->_select)));

$fields = '';

foreach ($this->_select as $s)

{

if (is_array($s))

{

if ($s[0] instanceof \Database_Builder_Select)

{

$fields .= ' ('.$s->compile().') AS '.$s[1].' ,';

}

elseif ($s instanceof \Database_Expression)

{

$fields .= ' ('.trim($s[0]->value(), ' ()').') AS '.$s[1].' ,';

}

else

{

$fields .= $db->quote_identifier($s).' ,';

}

$query .= substr($fields, 0, -1);

$fields = null;

return : SELECT "u"."id" ,"u"."username" ,(SELECT count(id) FROM "acl_users_groups" WHERE "id" = u.group_id) FROM "acl_users" AS "u"

it work but ugly!

can you please check this change in select.php compile function?

atabak

sorry it work with 1.9

Harro

Ok.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion