left Join between two tables

jiflorido

Hi, Im try to do a left Join between two tables

table 1 products_shop

id_product | price
1 | 34$

table 2 product

id_product . | . reference
1 | Pen

Im try that the result will be this table

Id_product . | reference . . | price
1 . | pen . | 34€

With this code

Editor::inst( $db, 'product_shop')
                ->fields(
         Field::inst( 'product_shop.id_product' ),
         Field::inst( 'product_shop.price' ),
         Field::inst( 'product.reference' )
     
                )
                ->leftJoin('reference as primary', 'product_shop.id_product', '=', 'product.id_product' )
                ->process( $_POST )
                ->json();

show a errror

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary ON product_shop.id_product = product.id_product at line 1

where is the problem? Thank you very much for the help.

jiflorido

whit this code

Editor::inst( $db, 'product_shop')
    ->fields(
         Field::inst( 'product_shop.id_product' ),
         Field::inst( 'product_shop.price' )
                        ->options( Options::inst()
                        ->table( 'product' )
                                        ->value( 'id_product' )
                                        ->label( 'reference' )),
         Field::inst( 'product.reference' )
      
    )
    ->leftJoin('product', 'product.id_product', '=', 'product_shop.id_product' )
    ->process( $_POST )
    ->json();

No show error.. but don´t show any result in table..

allan

Can you add the debug() option:

Editor::inst( $db, 'product_shop')
    ->debug( true )
    ->fields(
         Field::inst( 'product_shop.id_product' ),
         Field::inst( 'product_shop.price' )
                        ->options( Options::inst()
                        ->table( 'product' )
                                        ->value( 'id_product' )
                                        ->label( 'reference' )),
         Field::inst( 'product.reference' )
       
    )
    ->leftJoin('product', 'product.id_product', '=', 'product_shop.id_product' )
    ->process( $_POST )
    ->json();

Then use the debugger to take a trace of the table when it loads and let me know what the debug code is please?

Thanks,
Allan

jiflorido

Thanks allan,

this is the debug code ?

https://debug.datatables.net/ezecen

allan

Thanks for the debug code. It shows that DataTables hasn't made an Ajax request at the time when the debugger was run.

Are there any errors shown in your browser's console?

Thanks,
Allan

jiflorido

Thank allan i do changes..

with this code..

Editor::inst( $db, 'ps_product_shop')
    ->debug( true )
    ->fields(
         Field::inst( 'ps_product_shop.id_product' )
                        ->options( Options::inst()
                        ->table( 'ps_product' )
                        ->value( 'id_product' )
                        ->label( 'reference' )),
         Field::inst( 'ps_product_shop.price' ),
         Field::inst( 'ps_product.reference' )
        
    )
    ->leftJoin('ps_product', 'ps_product.id_product', '=', 'ps_product_shop.id_product' )
    ->process( $_POST )
    ->json();

show this error..

DataTables warning: table id=example - Requested unknown parameter 'id_product' for row 0, column 1. For more information about this error, please see http://datatables.net/tn/4

and this ( show 2 error )

DataTables warning: table id=example - Requested unknown parameter 'id_product' for row 0, column 1. For more information about this error, please see http://datatables.net/tn/4

the debug code is this...

https://debug.datatables.net/iduzub

What do you think it can be? they are only two tables ..

thank you very much for your help.

jiflorido

This is my SQL query

SELECT ps_product_shop.id_product,ps_product_shop.price,ps_product.reference FROM ps_product_shop INNER JOIN ps_product ON ps_product_shop.id_product = ps_product.id_product;

and the result that i want

id_product   |          price        |        reference
593              |     17.000000    |   Shapers Block: Soft
594              |     17.000000    |     Shapers Block: Medium
595              |     17.000000    |     Shapers Block - Medium Hard

jiflorido

I do it!

now is working. Thanks!!!