1

Consider the following Query in RQB using Foreign Keys

I have two tables, companies and weather.

The foreign key joins the weather to company on zip code

The RQB I am entering is:

Employees Total - Greater Than - 1000

OR

FK Weather.Temp < 50

The SQL that is generated by RQB is (approximately):

SELECT * FROM companies
INNER JOIN weather
ON companies.zip = weather.zip
WHERE company.employees > 1000
OR
weather.temp < 50

The issue is that from the UI perspective if I specify an OR and use a foreign key in the OR WHERE clause, I should get a LEFT JOIN instead of an INNER JOIN. I would expect the INNER JOIN in the case of an AND, but an OR requires a LEFT JOIN so I get all the companies with Employees > 1000 and I also get all of the company records with a match of the company records where Temp was < 50

My question is, is there a way to get RQB to output a LEFT JOIN on a foreign key where the referenced table is used with an OR?

Severun
  • 2,893
  • 1
  • 16
  • 22
  • Why would you expect a `left join`? A `full outer join` would make sense, but why prefer one table over the other? – Gordon Linoff Mar 20 '15 at 02:27
  • I believe you are correct, but full outer join is not supported by MySQL (which I'm currently using), which complicates the problem a bit. You can emulate it by something like: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id – Severun Mar 20 '15 at 16:30

1 Answers1

0

Not with the current code I'm afraid. Best to raise an issue?

Could start to get tricky as that join could go on to be used by other things? Although nulls should resolve to false.

salk31
  • 995
  • 2
  • 8
  • 13
  • I will raise an issue, but I'm not sure it's solvable in a technically correct way, per Gordon's comment above. It would need to be emulated, per my follow-up comment. I'll open an issue on github and we can discuss it further and see if there's a solution that's not a huge deal. – Severun Mar 20 '15 at 16:33
  • I'm not very familiar with mysql. How about subqueries? – salk31 Mar 20 '15 at 18:14
  • A left join union'd w/ a right join achieves the same affect. I put details in the issue on github https://github.com/salk31/RedQueryBuilder/issues/35 – Severun Mar 20 '15 at 22:51