How to Join tables based on a variable?

Martimmy

Hello
I have a parentTable and need to LeftJoin a childTable. So far so good. My problem is that the childTable needs to be “filtered” based on a variable on ServerSide.

Let us say I have defined a (large) checklist in the parentTable and need to store the results in the childTable based on the serialNumber of an item which is to be checked. The checklist can be used for multiple type of items.

I figured out that I can’t use a variable in the “hack” for multiple condition of a LeftJoin (I guess a column I mandatory) and an ordinary ->where() condition results in missing lines of the parentTable which aren’t “checked”.
Because of my experiences with the LeftJoin can I expect the same problem with Mjoin?

Has anyone a hint for a solution? This can’t be so difficult as it is for me at the moment ?

kthorngren

Not sure exactly what you have but maybe this blog will help:
https://datatables.net/blog/2016-03-25

Kevin

Martimmy

Hi Kevin,

thanks for your reply. I just realized that I posted in wrong category. I’m using editor and can not provide a testcase yet.

I’m afraid the blog entry does not fit my problem.

My checklist has 4500 lines and I have a couple items to check. My fist emergency solution could be to load the whole childTable and use a filter. I’m afraid this results in some performance issues.

Is there no way to perform a “->where()” condition on the child table which is joined?

Robert

allan

Can you show me what you are attempting to do in SQL please? I'm not quite clear on how it all fits together:

My problem is that the childTable needs to be “filtered” based on a variable on ServerSide.

Sounds like you just need a ->where() condition - e.g. ->where('myColumn', $myVariable ).

Allan

Martimmy

Hi Allan,

I don’t know what I can further describe as I did in my first post.
Maybe a query helps:

SELECT * FROM dbo.checklist LEFT JOIN dbo.results ON dbo.checklist.id = dbo. results.ref_id AND dbo. results.ref = 'N056'

‘N056’ is my variable.

My experience with ->where() on an childTable is that it results in missing lines of the parentTable.

allan

Yes, the SQL helped - thank you! You are looking for a multiple condition join. That unfortunately is something that is not directly supported by Editor's PHP libraries, but there is actually a workaround (/hack depending on how you want to look at it!) which is described here.

Possibly a better alternative is to use a VIEW which would do what you need, but requires a little more setup.

Allan

Martimmy

As described in my first post: I’m getting an error massage that a "column" is necessary for the hack. I couldn’t use the hack with a variable yet. Can you conform that it is possible?

Is there a way to pass a variable to a defined VIEW in a SQL database?

Robert

allan

Ah - darn. Actually thinking about it, no there isn't for a situation like that. You can add where() conditions no problem (which is what I was thinking earlier), but that's not going to work here.

I'm sorry, it looks like the libraries don't actually have a method to do a multiple condition join at the moment. You'd need to split it into the join and then the where condition.

Perhaps you could add ->debug(true) so we can see what the SQL is being generated is? (Add it just before the ->process() call and look at the returned JSON).

Allan

Martimmy

You'd need to split it into the join and then the where condition can you give me an example based on my SQL query?

allan

I was thinking like this:

SELECT *
FROM  dbo.checklist
LEFT JOIN  dbo.results ON dbo.checklist.id = dbo.results.ref_id
WHERE  dbo.results.ref = 'N056'

That might or might not give you what you are looking for...

Allan