Problem in $query->leftJoin();

[flanderboy]

Hi everyone, I have a query similar to this one

$query = $fpdo->from('views'); $query = $query->select(NULL); $query = $query->select('SUM(views.views) AS views, 0 AS clicks, 0 AS leads, 0 AS sales, SUM(views.commission) AS commission, program.id'); $query = $query->leftJoin('programs ON programs.id = views.program_id'); $query = $query->where('programs.status IN ('OK','KO','NEW')'); $query = $query->where('views.date >= ? AND views.date <= ?', $period['from'], $period['to']); $rs = $query->fetchAll();

SELECT SUM(views.views) AS views, 0 AS clicks, 0 AS leads, 0 AS sales, SUM(views.commission) AS commission, program.id FROM views LEFT JOIN programs ON programs.id = views.program_id WHERE programs.status IN ('OK','KO','NEW') AND views.date >= '' AND views.date <= '';

in FluentPDO v1.1.1 if i try this

$query = $fpdo->from('views'); $query = $query->select(NULL); $query = $query->select('IFNULL(SUM(views.views),0) AS views, 0 AS clicks, 0 AS leads, 0 AS sales, SUM(COALESCE(views.commission, 0.00)) AS commission, program.id'); $query = $query->leftJoin('programs ON programs.id = views.program_id'); $query = $query->where('programs.status IN ('OK','KO','NEW')'); $query = $query->where('views.date >= ? AND views.date <= ?', $period['from'], $period['to']); $rs = $query->fetchAll();

the query works

SELECT IFNULL(SUM(views.views),0) AS views, 0 AS clicks, 0 AS leads, 0 AS sales, SUM(COALESCE(views.commission, 0.00)) AS commission, program.id FROM views LEFT JOIN programs ON programs.id = views.program_id WHERE programs.status IN ('OK','KO','NEW') AND views.date >= '' AND views.date <= '';

after update on FluentPDO v1.1.2 doesn't work and the debug write a query like this

SELECT IFNULL(SUM(views.views),0) AS views, 0 AS clicks, 0 AS leads, 0 AS sales, SUM(COALESCE(views.commission, 0.00)) AS commission, program.id FROM views LEFT JOIN programs ON programs.id = views.program_id LEFT JOIN ON .id = views._id WHERE programs.status IN ('OK','KO','NEW') AND views.date >= '' AND views.date <= '';

LEFT JOIN ON .id = views._id is added by FluentPDO

Did I make any mistakes? Stefano

[hapm]

It's not the solution, but FluentPDOs smart join functionality is probably the cause fir your problem. You can try to use $query = $fpdo->from('views')->disableSmartJoin(); as a work around.

[flanderboy]

yes, is not a solutions because i can't change all queries in my application.

[hapm]

Found the problem: the table in program.id isn't known and therefor FluentPDO tries to join it. Use program_id instead. This is not a bug.

[flanderboy]

For me is a bug becouse in v1.1.1 works and in v1.1.2 don't and, again, i can't change all queries in my application.

Other observation, if i explicit $query->leftJoin('programs ON programs.id = views.program_id'); i mean "do not use smart join". If i want "smart join" i not explicit ->leftJoin() but i use the syntax of "smart join"

[flanderboy]

I removed COALESCE() from query and works in v1.1.2

[hapm]

Ok, tried the regex of 9fdade2f and it looks like the new regex allows tablenames starting with a number. That means it parses 0.00 as a table name. That actually is a bug! Tested with this regex: /([^[:space:]\(\)0-9][^[:space:]\(\)]*[.:])[\p{L}\p{N}\p{Pd}\p{Pi}\p{Pf}\p{Pc}]*/u and it worked quite well, but I probably will not find the time to submit a pull request.

[cbornhoft]

Thanks for reporting this!

This is not a bug, however. 0.00 is a valid table name in MySQL and likely other dialects as well. Should it ever be a table name? Probably never; but it is allowed for whatever reason. We'll take a look and see if we can implement a solution.

The core issue here is how Fluent currently searches for tables, and we're looking to completely refactor that in 2.0.

Just curious @flanderboy, what version of PHP are you running?

[flanderboy]

Hi Chris, this is may version of PHP

PHP 7.0.18-0

Thanks to you Stefano

[hapm]

@cbornhoft wrote:

Should it ever be a table name? Probably never; but it is allowed for whatever reason.

Any SQL dialect I know of needs to escape this kind of table names in some type of quotes (like [] or `` or lets stick with the standard ""), because else there is a ambiguity with numeric values. And as numeric values happen to be much more often in an sql query than there are such nasty table names, imho FluentPDO should ignore them for now, and handle them properly when it's ready for that.

@flanderboy Another workaround for the problem: replace the 0.00 value against a parameter ? and handover 0 as a value when you execute the query. You only need to fix queries where there are magic floating point values in the query it self.