jeudi 20 février 2020

Made a query using two databases Laravel Query Builder

I have a question about query builder.

I wanna made a query that will have conection with two databases

I will show you the SQL query :

SELECT
naviones_db16.ps_orders.payment,
naviones_db16.ps_orders.total_paid AS total,
naviones_db16.ps_orders.current_state AS current_state,
naviones_db16.ps_orders.id_order AS id_order,
naviones_db16.ps_shop.NAME AS name_shop,
naviones_db16.ps_customer.firstname AS customer_firstname,
naviones_db16.ps_customer.lastname AS customer_lastname,
naviones_db16.ps_orders.reference,
naviones_db16.ps_orders.date_add AS fecha
FROM
ps_orders
INNER JOIN naviones_db16.ps_customer ON 
 naviones_db16.ps_customer.id_customer= 
 naviones_db16.ps_orders.id_customer
 INNER JOIN naviones_db16.ps_shop ON naviones_db16.ps_shop.id_shop = 
 naviones_db16.ps_orders.id_shop
 WHERE
 naviones_db16.ps_orders.current_state = 16
 AND 
 naviones_db16.ps_orders.id_order NOT IN (
 SELECT
    multimarket.productos.id_order
FROM
    multimarket.productos
WHERE
    multimarket.productos.id_order = naviones_db16.ps_orders.id_order
)
 ORDER BY
   naviones_db16.ps_orders.id_order DESC

This is that i have done in query builder

$naviones = DB::connection('naviones')->table('ps_orders')
        ->select('ps_orders.payment', 'ps_orders.total_paid AS total', 'ps_orders.current_state AS current_state', 'ps_orders.id_order AS id_order',
            'ps_shop.NAME AS name_shop', 'ps_customer.firstname AS customer_firstname', 'ps_customer.lastname AS customer_lastname', 'ps_orders.reference',
            'ps_orders.date_add AS fecha')
        ->join('ps_customer', 'ps_customer.id_customer', '=', 'ps_orders.id_customer')
        ->join('ps_shop', 'ps_shop.id_shop', '=', 'ps_orders.id_shop')
        ->where('ps_orders.current_state', '=', '16')
        ->orderBy('id_order', 'DESC')
        ->get()->toArray();

Of course, i have other conection this is: DB::connections('multimarket') but this point i dont have any idea how i could do that. Do you have any idea?

Thank u



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire