Missleading select example

[duzun]

My first experience with Plusql wasn't that good as it could be.

At first glance there is nothing complicated or strange about this example:

//get an escape function
$f = Plusql::escape($profile);

echo Plusql::from($profile)->strong_guy
                           ->weak_guy->select('strong_guy_id,weak_guy_id,strong_name,weak_name')
                           ->where('strong_guy_id = '.$f($_GET['something']))
                          ->orderBy('strong_guy_id,weak_guy_id);

But it made me think it uses USING(strong_guy_id) internally and I started writting queries with this conviction in my mind, just to discover later that actually this query can't be run as is

It translates to

SELECT strong_guy_id,weak_guy_id,strong_name,weak_name 
FROM strong_guy 
INNER JOIN weak_guy ON strong_guy.strong_guy_id = weak_guy.strong_guy_id 
WHERE strong_guy_id = 1 
ORDER BY strong_guy_id,weak_guy_id

Which results in an error:

#1052 - Column 'strong_guy_id' in field list is ambiguous

[iaindooley]

Thanks Dumitru. The error isn't that it doesn't use USING, but that it
doesn't put, eg. strong_guy.strong_guy_id in the "from" clause (ie. The
string passed into the select() method).

Still needs to be fixed of course... Also it'll need to be added into the
WHERE clause.

Thanks for picking it up I'll fix up the docs.

Iain
On Sep 3, 2015 5:06 AM, "Dumitru Uzun" notifications@github.com wrote:

My first experience with Plusql wasn't that good as it could be.

At first glance there is nothing complicated or strange about this example:

//get an escape function$f = Plusql::escape($profile);echo Plusql::from($profile)->strong_guy ->weak_guy->select('strong_guy_id,weak_guy_id,strong_name,weak_name') ->where('strong_guy_id = '.$f($_GET['something'])) ->orderBy('strong_guy_id,weak_guy_id);

But it made me think it uses USING(strong_guy_id) internally and I
started writting quesries with this conviction in mi mind, just to discover
later that actually this query can't be run as is

It translates to

SELECT strong_guy_id,weak_guy_id,strong_name,weak_name FROM strong_guy INNER JOIN weak_guy ON strong_guy.strong_guy_id = weak_guy.strong_guy_id WHERE strong_guy_id = 1 ORDER BY strong_guy_id,weak_guy_id

Which results in an error:

#1052 - Column 'strong_guy_id' in field list is ambiguous

—
Reply to this email directly or view it on GitHub
#3.

[iaindooley]

Hi Dumitru, I've commited a change that will resolve this now (although having said that I haven't tested it ;) But basically I just put the table name on the strong_guy_id field in the select, where and order clauses.

Thanks!

[duzun]

Hi Iain,
It looks good. I've done exactly the same thing, only with a different DB, but with a similar structure.
At least now one doesn't imagin there is a USING behind the scene like I did :-)

[iaindooley]

If you had a USING instead of an ON clause, you would still get the same
ambiguous field error if you didn't prefix the field name with the table
name in the FROM, WHERE and ORDER clauses ...

On Fri, 4 Sep 2015, Dumitru Uzun wrote:

Hi Iain,
It looks good. I've done exactly the same thing, only with a different DB, but with a similar structure.
At least now one doesn't imagin there is a USING behind the scene like I did :-)

—
Reply to this email directly or view it on GitHub.[AAVtmirN7z4-OuQD81SkAXS-xwhzQWMAks5ouVWYgaJpZM4F2vYK.gif]

[duzun]

I'm not sure I understand what you mean, but I can run this query (MySQL 5.5.44):

SELECT strong_guy_id,weak_guy_id,strong_name,weak_name 
FROM strong_guy 
INNER JOIN weak_guy USING(`strong_guy_id`)
WHERE strong_guy_id = 1 
ORDER BY strong_guy_id,weak_guy_id

If I had one more ambiguous field that is not part of USING, I would have to prefix it with table name, but in this case MySQL is "aware" of the fact that strong_guy_id has the same value in both tables in the context of this query, thus no ambiguity.

[iaindooley]

Oh yeah, you're right. If you have USING then you don't need to prefix each argument with the table name. It would actually be pretty easy for me to change the query builder to use USING instead of ON since it maps the relationships using the key names anyway ... I'll get around to this (at some point!) -- If you want to contribute the fix I'd appreciate it! In the meantime we'll just leave the example so that it doesn't suggest it's using USING behind the scenes. I'll reopen this now and add it to the "TODO" list in the README :)