Couple of Oddball leftJoin Questions (diff't tbl names & targeting a particular ID)

shawngibson · January 2023

Hi All, Happy Saturday:)

Using my functioning code as a template:

Field::inst('products.subcat')
            ->options(
                Options::inst()
                    ->table('categories.subcats')
                    ->value('id')
                    ->label('subcat')
            )
            ->validator(Validate::dbValues()),
        Field::inst('subcats.subcat')

->leftJoin('categories.subcats', 'categories.subcats.id', '=', 'products.subcat')

...I am trying to do something similar elsewhere, but with 2 distinctions:

1) The main table name and leftJoin table name are necessarily different.

2) I need to home-in my results to only a particular ID in the leftJoin table.

I have a Products database, a Purchases database, and an external Contacts database.

In the Products db I have a table called Manufacturers, similarly in the Purchases db I have a table called Vendors; finally,in the Contacts db, I have a table called Contacts, including a field called ContactType. ContactType includes records such as "Vendor, Manufacturer, Contractor" etc.".

If we forget all that for a moment, and disregarding the Purchases/Vendor altogether, using the same structure as the code above, let's pretend that the Manufacturers table in the Products db was named "Contacts" instead. In this case, the join would be easy:

Field::inst('products.contact')
    ->options(
        Options::inst()
            ->table('contacts.contacts')
            ->value('id')
            ->label('contact')
    )
    ->validator(Validate::dbValues()),
Field::inst('contacts.contact')

->leftJoin('contacts.contacts', 'contacts.contacts.id', '=', 'products.contact')

This would show me ALL contacts in my Select code.

Unfortunately, as alluded to, in reality, I need to take the Manufacturers table in Products and leftJoin to it the Contacts table (in the Contacts db). So with the above code, I need to reference Manufacturers and somehow indicate that the leftJoin table is actually Contacts.

On top of that, I only want to display results with a Manufacturer (and no Vendor, Contractor, etc. records).

I'm venturing into modifications that I wouldn't know where to begin to code, or if it's even possible. Something like "after leftJoining Contacts to Manufacturers, only include records with an ID of 4", 4 being the ID of record "Manufacturer".

I truly apologize for the verbosity of my question, but it all boils down to the two bullet points up top...in case the details thereafter make it sound like a whole bunch more:(

Thanks as always,
Shawn

rf1234 · January 2023

You can't do a left join in the options instance but you have a couple of alternatives:
a) use an implicit 1990's style INNER JOIN through the WERE clause
b) use self written SQL using Editor's db handler to retrieve the options

Here is a thread that handles this all in depth.

https://datatables.net/forums/discussion/comment/215634/#Comment_215634

And another one:
https://datatables.net/forums/discussion/comment/116150/#Comment_116150

shawngibson · January 2023

Well, I figured out the first half. The below code allows me to pull in the manufacturer name from the contacts database and push it into my products table, by selecting the name of the manufacturer for the current product:

Field::inst('products.manufacturer')
    ->options(
        Options::inst()
            ->table('contacts.contacts')
            ->value('first_name')
            ->label('first_name')
    )
    ->validator(Validate::dbValues()),
Field::inst('contacts.first_name')

->leftJoin('contacts.contacts', 'contacts.contacts.first_name', '=', 'products.manufacturer')

And the appropriate JavaScript:

//EDITOR
{
     "label": "Manufacturer:",
     "name": "products.manufacturer",
     "type": "select",
     "placeholder": "Manufacturer"
}

//DATATABLES
//replaces "products.manufacturer"
{
     "data": "contacts.first_name"
}

In getting this far, I came to realize that I have previously split ContactType out into its own table, with a leftJoin into the Contact db, i.e., I enter all of a particular contact's details (first-or-business name + last name, full address, website, email, etc.) into the Contacts database, and select the Contact Type from the leftJoined ContactTypes table, allowing me to choose "Manufacturer", "Vendor", or "Contractor" etc. for each Contact.

So now, I just need to refine my code above to have it only include results where the field Type in the Contacts db has a value of "2", which is the ID of "Manufacturer" in the ContactTypes db.

To that end, I'm scouring all of the datatables.net search results for "leftJoin"...wish me luck lol:)

Shawn

shawngibson · January 2023

Would this code be appropriate if I want to show only the results where the field "type" in the Contacts db is a value of 2?

->where( function ($q) {
            $q->where( 'type', '2%', 'LIKE' );
        }

I ask here because the Contacts db is currently empty, so I should defer to a programmer here...

shawngibson · January 2023

Hi rf1234, my apologies; I was writing my responses as you offered your own response and I am just seeing it now. I will check out your links...thanks very much:)
Shawn

rf1234 · January 2023

Never mind! I also got stuck with this a couple of years ago. In hindsight I should have dropped those elaborate Editor options instances and go for custom functions using my own SQL like in this example. An options instance is not CRUD. It is just R ...

Field::inst( 'my_suppliers.SupplierID' )
    ->options( function () use ( $db ) {
         $stmt =   ('SELECT DISTINCT a.SupplierName    AS label,
                                     b.MySupplierID id AS value
                       FROM suppliers a
                 INNER JOIN my_suppliers b ON a.SupplierID = b.SupplierID
                   ORDER BY 1 ASC');
          $result = $db ->raw() ->exec($stmt);
          return $result->fetchAll(PDO::FETCH_ASSOC);
    } );

allan · January 2023

Yes - the Options class is intentionally simple for the 80% use cases. For anything a little more complex, a custom query is the way to go.

That said(!) Editor 2.1 is going to introduce the ability to do a left join with the chaining API as that is a real missing feature at the moment. That, together with the existing where() method, will hopefully cover almost all uses of the class.

Allan

shawngibson · January 2023

Thanks Guys.

Is there a potential release date for Editor 2.1?

Just to be clear, are you saying my where() code above won't work...for fundamental reasons (ATM)? To recap, it would be this, below, with the aim of narrowing results down to only cases where the "type" = "2" (which is the ID of "Manufacturers" in the co-relative leftJoined ContactTypes table):

Field::inst('products.manufacturer')
    ->options(
        Options::inst()
            ->table('contacts.contacts')
            ->value('first_name')
            ->label('first_name')
            ->where( function ($q) {
                 $q->where( 'type', '2%', 'LIKE' );
             }
    )
    ->validator(Validate::dbValues()),
Field::inst('contacts.first_name')
 
->leftJoin('contacts.contacts', 'contacts.contacts.first_name', '=', 'products. Manufacturer')

This code works perfectly right now (minus the where () code), save the fact it shows all contacts in the select as opposed to only records where "type" = "2".

rf1234 · January 2023

The WHERE clause works right now, for the entire Editor and for the options instance. The only restriction there is right now is that the WHERE clause in the options instance must be a closure function.

allan · January 2023

Is there a potential release date for Editor 2.1?

Shouldn't be too far away - early February I hope. I plan to implement that specific feature in the PHP libraries later this week though, so you'll be able to use that using the pre-release libraries.

Allan

shawngibson · January 2023

Very good, and thank you both:)

My Editor subscription ran out a few months ago, and I'm still unemployed after Covid layoffs, but I should have some funds in a week or two with which I can renew my Editor subscription and hopefully purchase a few PHP, JQuery and SQL/Mariadb online-courses. My coding skills/intuition are a solid C- lol! (But I never let that stop me...)

allan · January 2023

Sent you a PM. Also, given what you are doing here, I'd say you are well above a C

Allan

allan · January 2023

That's the left join for Options committed for the PHP libraries .

There are a few changes in various files, so probably best to just grab a zip / clone of the current code. Then you can do stuff like:

            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'sites.id' )
                ->label( ['name', 'first_name'] )
                ->leftJoin('users', 'users.id', '=', 'sites.id')

Allan

shawngibson · January 2023

Thanks Allan, I just downloaded a 'controls template' db via Generator with a field for each of the different types (checkbox, radio, password, etc.) including Editor and all options checked. But I'll go to the Editor page and download the package again, with a big thanks for that:)

I realize now that it makes much more sense to use a checkbox when choosing the Type of a new Contact, since sometimes one contact is both a Manufacturer and Vendor, for example.

But having gone through the examples, manual, reference and search pages here, it appears I would somehow have to turn the checkbox options{} into some sort of 1-to-x deep array.

In other words, simply, with my Contacts table, can my JS be modified from the below code to code which will read the data and output to a checkbox?

//EDITOR JS
            {
                label: "Type:",
                name: "contacts.type",
                type: "select",
                placeholder: "Contact/Supplier Type"
            }
 
 
//DATATABLES JS
            {
                "data": "contacttypes.type"
            }

//PHP
        Field::inst( 'contacts.type' )
            ->options( Options::inst()
                ->table( 'contacttypes' )
                ->value( 'id' )
                ->label( 'type' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'contacttypes.type' )
    )
    ->leftJoin( 'contacttypes', 'contacttypes.id', '=', 'contacts. Type' )

allan · January 2023

I would have expected that to work with a radio type. Changing type: 'select' to type: 'radio' should be all that is needed there.

If that isn't the case, can you give me a link to your page so I can take a look into it please?

Thanks,
Allan

Couple of Oddball leftJoin Questions (diff't tbl names & targeting a particular ID)

Couple of Oddball leftJoin Questions (diff't tbl names & targeting a particular ID)

Answers

Howdy, Stranger!

Categories

DataTables

Couple of Oddball leftJoin Questions (diff't tbl names & targeting a particular ID)

Couple of Oddball leftJoin Questions (diff't tbl names & targeting a particular ID)

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables