Codeigniter querying multiple databases in same query

[eluser]behnampmdg3[/eluser]
Hi;

I know I can use multiple databasaes. But in this situation I have multiple databases IN THE SAME QUERY.

Code:

SELECT *
FROM   portal.orders
       INNER JOIN portal.orderproducts
               ON portal.orders.id = portal.orderproducts.order_id
       INNER JOIN h2g.packages_daily_deal
            ON
                   ( portal.orderproducts.product_id = h2g.packages_daily_deal.crm_id
                   OR
                   portal.orderproducts.product_id = h2g.packages_daily_deal.crm_id_thd
                   )  
       INNER JOIN portal.customers
               ON portal.orders.customer_id = portal.customers.id
WHERE
       ( portal.orders.brand = '432' OR portal.orders.brand = '123' )
    AND portal.orders.reported_to_mwa <> 1
       AND portal.orders.status_id = 5
       GROUP BY portal.orders.id
       ORDER BY email

How can I do that?

[eluser]ivantcholakov[/eluser]
If you insist to use the query builder, this example is possible with CI 3.0-dev.

Code:

$po = 'portal.orders';
$pop = 'portal.orderproducts';
$pdd = 'h2g.packages_daily_deal';
$pc = 'portal.customers';

$sql = $this->db
    ->select()
    ->from($po)
    ->join($pop, "$po.id = $pop.order_id", 'inner')
    ->join($pdd, "$pop.product_id = $pdd.crm_id OR $pop.product_id = $pdd.crm_id_thd", 'inner')
    ->join($pc, "$po.customer_id = $pc.id", 'inner')
    ->group_start()
        ->where("$po.brand", '432')
        ->or_where("$po.brand", '123')
    ->group_end()
    ->where("$po.reported_to_mwa <>", 1)
    ->where("$po.status_id", 5)
    ->group_by("$po.id")
    ->order_by("$po.email", 'asc')  // I am guessing the table here.
    ->get_compiled_select();

echo $sql;

/*
SELECT *
FROM `portal`.`orders`
INNER JOIN `portal`.`orderproducts` ON `portal`.`orders`.`id` = `portal`.`orderproducts`.`order_id`
INNER JOIN `h2g`.`packages_daily_deal` ON `portal`.`orderproducts`.`product_id` = `h2g`.`packages_daily_deal`.`crm_id` OR `portal`.`orderproducts`.`product_id` = `h2g`.`packages_daily_deal`.`crm_id_thd`
INNER JOIN `portal`.`customers` ON `portal`.`orders`.`customer_id` = `portal`.`customers`.`id`
WHERE   (
portal.orders.brand  = '432'
OR portal.orders.brand  = '123'
)
AND `portal`.`orders`.`reported_to_mwa` <> 1
AND `portal`.`orders`.`status_id` = 5
GROUP BY `portal`.`orders`.`id`
ORDER BY `portal`.`orders`.`email` ASC
*/

[eluser]behnampmdg3[/eluser]
Not really sure what you did up there. You mind explaining?

Thanks

[eluser]ivantcholakov[/eluser]
1. I did not check before whether on MySQL level joining between two tables from different databases is possible. Well, it is possible - http://stackoverflow.com/questions/56983...-databases. So the example I've given is valid in this regard.

2. You have to make a connection to one of your databases, which is the main for your application. And when you join to a table from the other database, as I can see, you use fully qualified table/field names. Then, you have two options to implement the query:

2.1. To pass the query directly to the database server - http://ellislab.com/codeigniter/user-gui...eries.html. I this case, if there are some parameters by that you modify the query, you have to manually make proper escaping to avoid SQL injections - see "Escaping Queries" on the same page. If you use CodeIgniter 2.x version, this is the way for such a complex query.

2.2. On CodeIgniter 3.0-dev, for your example you may use the so called "query builder" (the former "active record" feature, just the name changes) - http://ellislab.com/codeigniter/user-gui...ecord.html. In this approach, by using a series of API-calls you instruct the corresponding library to create the SQL query for you, that suits to your database server. Escaping parameters (if there are any) is done automatically by the query builder.

I demonstrated the second way, which I prefer. This is the main reason for me to switch to CodeIgniter 3.0-dev, the query builder there is improved much, it can build real-life complex queries like in your example.