Double Joins

steven.stenzel

I was trying to join three tables together, and I have found that when I try to put a join inside the first join, I get an error where it tries to convert the second join to a string, and when I move the second join outside of the first join, it attempts to join to the first table instead. (Since it won't let me use table.column in the ->join syntax, this has left me wondering if double joins are possible or not.

'''
$editor = Editor::inst( $db, 'listings')
->fields(
Field::inst('listings.id'),
Field::inst('property_id'),
Field::inst('listings.listing_type')

            //      Field::inst('addresses.id'),
            //      Field::inst('addresses.street_number'),
            //      Field::inst('addresses.street_name'),
            //      Field::inst('addresses.street_type')
             )
            ->join(Join::inst('properties','object')
                    ->join('property_id','id')
                    ->fields(
                            Field::inst('id')->set(false),
                            Field::inst('size_type')
            ))
            ->join(Join::inst('addresses','object')
                    ->join('address_id','id')
                    ->fields(
                            Field::inst('id')->set(false),
                            Field::inst('street_name')

                    )
            )

'''

'''
$editor = Editor::inst( $db, 'listings')
->fields(
Field::inst('listings.id'),
Field::inst('property_id'),
Field::inst('listings.listing_type')

            //      Field::inst('addresses.id'),
            //      Field::inst('addresses.street_number'),
            //      Field::inst('addresses.street_name'),
            //      Field::inst('addresses.street_type')
             )
            ->join(Join::inst('properties','object')
                    ->join('property_id','id')
                    ->fields(
                            Field::inst('id')->set(false),
                            Field::inst('size_type')
                   )
                    ->join(Join::inst('addresses','object')
                           ->join('address_id','id')
                          ->fields(
                                 Field::inst('id')->set(false),
                                   Field::inst('street_name')
                              )
                    )
            )

'''

allan

Hi,

A double join isn't possible with the Join class because of how it operates (the join is actually done in PHP rather than in SQL, so all of the data required is not available).

However, a double join is possible with the newer leftJoin() method that was introduced in 1.3. Since you are using just object based data, I would recommend using that over the older join class as it is far more performant (the joins are doing in SQL).

There is documentation on the leftJoin() method available in the Editor documentation.

Allan

steven.stenzel

Hi Allan,

Thanks for the quick reply. I tried the leftJoin function first, however, using this method I was able to view columns on the 2nd and 3rd table, but I was unable to update the values. The ajax seemed to be returning only 1 primary key for the first cable, and was generating an error message that read something along the lines of "please contact your system administrater.

I think the post looked something like

[table1.id] = 11
[table1.value] = 'xyz'
[table2.id] = '20'
[table2.value] = 'abc'
[row_id] = row_1

I am going of memory here, but I can recreate the leftJoin scenario and give you the exact syntax and post/responce if it would help. I think it fell over when calling a .val() function.

Regards,
Steve

steven.stenzel

if(!empty($_GET) || !empty($_POST)){
        require_once('../../app/lib/datatables/DataTables.php');
        $editor = Editor::inst( $db, 'listings')
                ->fields(
                        Field::inst('listings.id'),
                        Field::inst('listings.property_id'),
                        Field::inst('listings.listing_type'),
                        Field::inst('properties.id'),
                        Field::inst('properties.size_type'),
                        Field::inst('addresses.id'),
                        Field::inst('addresses.street_name')
                 )
                ->leftJoin('properties', 'properties.id', '=', 'listings.property_id')
                ->leftJoin('addresses', 'addresses.id', '=', 'properties.address_id')
                ->process($_POST)
                ->data();

                echo json_encode( $editor);

action:edit

data[listings][id]:2

data[listings][listing_type]:executdive

data[properties][id]:2

data[properties][size_type]:ransge

data[addresses][id]:159

data[addresses][street_name]:Georgea

id:row_2

allan

Hi,

Thanks for the extra information. Looking at that, and the Editor code, it looks like it should actually work.

For the moment, can I just check the behaviour that you want. When you update a record in the listings table, do you actually want to change the values of the properties and addresses table, or do you just want to select the id for the already existing property / address? If you change the values, that will effect all records which use that property / address.

I'll try to reproduce the error in my local database using a double link as well.

Allan

steven.stenzel

Thanks again Allan,

I am trying to alter listing type, size_type, and street_name from 3 different tables. I can successfully do this with the older join method for 2 tables. There are additional fields on all tables, but I have simplified it while debugging. Just to clarify, I am not trying to use the dropdown option from the examples pages, but my goal is to be able to have a co-worker be able to correct spelling mistakes in address and property information without having to understand SQL. We can safely assume 1 listing = 1 property = 1 address.

Regards,
Steve

allan

Hi Steve,

Thanks for the clarification. Might you be able to e-mail me the SQL schema that you are using (allan@ this domain.net) as I'm having a few problems recreating the error.

Thanks,
Allan