A
A
ask0generator2014-03-25 12:56:08
MySQL
ask0generator, 2014-03-25 12:56:08

Kohana framework - multiple joins of subqueries in query builder - how to write a query?

How to write a query builder query so that it generates a query like this:

SELECT * FROM (
                    (SELECT id as locale_id, value as locale FROM a3_locale) t1
                        LEFT JOIN
                    (SELECT locale_id AS loc_id, var_id, value FROM a3_var_locale WHERE var_id = 1) t2
                        ON t2.loc_id = t1.locale_id
            )

I do it like this:
$query_locales = DB::select( array('value', 'locale'), array ('id', 'locale_id') )
            ->from("{$pre}locale");

        $query_var_locales = DB::select( array('locale_id', 'loc_id'), 'value', 'var_id' )
            ->from("{$pre}var_locale")
            ->where('var_id', '=', $id);

        $query_locales_vars_join = DB::select()
            ->from(array($query_locales, "t1"))
            ->join(array($query_var_locales, "t2"), "LEFT")
            ->on("t2.locale_id", "=", "t1.loc_id");

Returns incorrectly generated:
SELECT * FROM <b>['(']</b>
                    (SELECT `value` AS `locale`, `id` AS `locale_id` FROM `a3_locale`) AS `t1`
                        LEFT JOIN
                    (SELECT `locale_id` AS `loc_id`, `value`, `var_id` FROM `a3_var_locale` WHERE `var_id` = 3) AS `t2`
                        ON
                    (`t2`.`locale_id` = `t1`.`loc_id`)
<b>[')']</b>

In "[ ]" I displayed what was missing in the request so that it would work.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question