Trying to do a join on 3 tables.

classic12

Struggling as I can't get my head around how the joins work in the examples.

I have 3 tables.

quotes ( same structure as users except the Unique id = quoteID)
user_files ( same as samples )
files ( same as samples.)

I want to get the quote details along with the system_path from the files table.

The code fails without any errors so as usual I am blindly guessing.
Is there any other php options to show errors ?

Cheers
Steve Warby

<?php
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST');  
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
/*
 * Example PHP implementation used for the index.html example
 * Need to get a query to show the quote title from quotes and the images in a new row
 */

// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'quotes','quoteID' )
    ->fields(
        Field::inst( 'quotes.quoteID' )->set( false ),
        Field::inst( 'quotes.custID' ),
        Field::inst( 'quotes.quoteDate' ),
        Field::inst( 'quotes.quoteTitle' ),
        Field::inst( 'quotes.notes' ),
        Field::inst( 'quotes.notesInternal' ),
        Field::inst( 'quotes.status' )
        

    ),
         Field::inst( 'files.id' )
    
    ->leftJoin( 'files', 'files.id', '=', 'quotes.quoteID' )
    ->join(
        Mjoin::inst( 'users_files' )
            ->link( 'quotes.quoteID', 'users_files.user_id' )
            ->link( 'files.id', 'users_files.user_id' )
            ->order( 'filename asc' )
            ->fields(
                Field::inst( 'quoteID' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'system_path' )
            )
    )
    
    

    ->process( $_POST )
    ->json();

tangerine

Your browser's Console should show error info.

allan

There is a syntax error in the PHP above in line 38. The Field:inst() should be inside the fields() method's parameters.

Your server's error logs should contain a PHP syntax error stating that.

Allan

classic12

Thanks alan got a join working producing what I need as follows:

"SELECT 
    a.quoteID, a.quoteTitle , a.notes,  c.web_path
    FROM quotes a 
    INNER JOIN users_files b on b.user_id = a.quoteID 
    INNER JOIN files c on c.id = b.file_id ORDER BY quoteID ASC"

This is okay as I am just calling this and not using the editor.

What is the correct syntax to do the same in your PHP files so I can use the editor.

Not understanding the syntax

Cheers

Steve Warby.

allan

This thread asked a similar question earlier today. Currently you need to use the workaround described here.

Allan