UNION and Filtering

Talk about Filter (search) Settings

Moderator: alexandrleonenko

cooperlegend
Newbie
Newbie
Posts: 8
Joined: Jan 7th, '14, 16:09

UNION and Filtering

Postby cooperlegend » Jan 7th, '14, 16:12

Hi,

I have a SQL SELECT which I would like to use that currently uses UNION to combine two selects.

This works fine except when filtering, when it appear the "WHERE" is only applied to the last SELECT of the UNION.

Any ideas how I can get this work with the filter working on all selects in the UNION

Thanks in Advance :)

John

administrator
Site Admin
Site Admin
Posts: 5997
Joined: Jan 7th, '09, 23:18
Contact:

Re: UNION and Filtering

Postby administrator » Jan 8th, '14, 06:54

Fist of all turn debug mode and check what happens and what error you see?

cooperlegend
Newbie
Newbie
Posts: 8
Joined: Jan 7th, '14, 16:09

Re: UNION and Filtering

Postby cooperlegend » Jan 8th, '14, 09:51

Hi, Thanks for the reply

I have already use debug, there are no errors.

But as stated above the WHERE part is only applied to the last part of the UNION , not all parts.

i.e.

SELECT players.playerid, players.active, players.name as playername, players.teamid, teams.name as team, players.handicap, tee FROM ((players LEFT JOIN groups AS groups ON groups.player1 = players.playerid) LEFT JOIN teams ON players.teamid = teams.teamid) LEFT JOIN competitions ON groups.competitionid = competitions.competitionid
UNION
SELECT players.playerid, players.active, players.name as playername, players.teamid, teams.name as team, players.handicap, tee FROM ((players LEFT JOIN groups AS groups ON groups.player2 = players.playerid) LEFT JOIN teams ON players.teamid = teams.teamid) LEFT JOIN competitions ON groups.competitionid = competitions.competitionid WHERE 1=1 ORDER BY playername ASC LIMIT 0, 100

This works fine until the competition filter is applied where by it becomes

SELECT players.playerid, players.active, players.name as playername, players.teamid, teams.name as team, players.handicap, tee FROM ((players LEFT JOIN groups AS groups ON groups.player1 = players.playerid) LEFT JOIN teams ON players.teamid = teams.teamid) LEFT JOIN competitions ON groups.competitionid = competitions.competitionid
UNION
SELECT players.playerid, players.active, players.name as playername, players.teamid, teams.name as team, players.handicap, tee FROM ((players LEFT JOIN groups AS groups ON groups.player2 = players.playerid) LEFT JOIN teams ON players.teamid = teams.teamid) LEFT JOIN competitions ON groups.competitionid = competitions.competitionid WHERE 1=1 AND ( LCASE(competitions.competitionid) = '1' ) ORDER BY playername ASC LIMIT 0, 100

As you can see the WHERE is only applied to the last SELECT ?!?!?

administrator
Site Admin
Site Admin
Posts: 5997
Joined: Jan 7th, '09, 23:18
Contact:

Re: UNION and Filtering

Postby administrator » Jan 9th, '14, 07:59

Try following: find in DataGrid.class.php SetFieldsFilteringInBind() method and following line of code:

Code: Select all

if(!preg_match('/\bwhere\b/i', $parsed_sql) && !preg_match('/\bhaving\b/i', $parsed_sql)) $this->sqlView .= ' WHERE 1=1 ';


then replace it with:

Code: Select all

if(!preg_match('/\bwhere\b/i', $parsed_sql) && !preg_match('/\bhaving\b/i', $parsed_sql)) $this->sqlView = '('.$this->sqlView.') WHERE 1=1 ';


Does it help?

cooperlegend
Newbie
Newbie
Posts: 8
Joined: Jan 7th, '14, 16:09

Re: UNION and Filtering

Postby cooperlegend » Jan 9th, '14, 09:13

Thanks again, but in the end I decided that this probably couldn't be fixed so I re-wrote my sql to work as a single SELECT

much like

SELECT DISTINCT players.playerid, players.active, players.name as playername, players.teamid, teams.name as team, players.handicap, tee FROM ((players LEFT JOIN groups AS groups ON (groups.player1 = players.playerid OR groups.player2 = players.playerid) LEFT JOIN teams ON players.teamid = teams.teamid) LEFT JOIN competitions ON groups.competitionid = competitions.competitionid WHERE 1=1 AND ( LCASE(competitions.competitionid) = '1' ) ORDER BY playername ASC LIMIT 0, 100

Putting the OR in the LEFT JOIN condition along with the DISTINCT in effect merged the two selects much like a UNION.

I hope this info helps other users

John

administrator
Site Admin
Site Admin
Posts: 5997
Joined: Jan 7th, '09, 23:18
Contact:

Re: UNION and Filtering

Postby administrator » Jan 12th, '14, 07:22

Thanks, anyway... we've added this issue to our TODO list for the next version.


Return to “Settings: Filtering (search) / Autocomplete”