Bug #84972 for SQL-Abstract-More: where = literal in join impossible?

Wed May 01 12:31:00 2013 xenoterracide [...] gmail.com - Ticket created

Subject:	where = literal in join impossible?
Date:	Wed, 1 May 2013 11:30:39 -0500
To:	bugs-sql-abstract-more [...] rt.cpan.org
From:	Caleb Cushing <xenoterracide [...] gmail.com>

http://stackoverflow.com/q/16307208/206466 I'm trying to do a join using SQL::Abstract::More that has an `and and then a literal value, not on a table column. =>{table.table_id=table_id,table_log.date>table.date,table_log.event_id=1} gd_audit_log the resulting output that I want LEFT OUTER JOIN table_log ON ( table_log.date > table.date AND table.table_id = table_log.table_id AND table_log.event_id = 1 ) this code works except for AND table_log.event_id = 1 the error is ... failed: Unknown column 'table_log.1' in 'on clause' obviously it's generating the wrong SQL, what I'm trying to figure out is how to get it to generate the SQL I need. p.s. not sure if this is a missing feature, bug or something I just don't know, any help would be appreciated. -- Caleb Cushing http://xenoterracide.com

Wed May 01 13:09:52 2013 laurent.dami [...] free.fr - Correspondence added

Subject:	Re: [rt.cpan.org #84972] where = literal in join impossible?
Date:	Wed, 01 May 2013 19:09:26 +0200
To:	bug-SQL-Abstract-More [...] rt.cpan.org
From:	Laurent Dami <laurent.dami [...] free.fr>

Le 01.05.2013 18:31, Caleb Cushing via RT a écrit : Show quoted text

> the resulting output that I want > > LEFT OUTER JOIN table_log ON ( > table_log.date > table.date > AND table.table_id = table_log.table_id > AND table_log.event_id = 1 > )

Hi Caleb, To insert a literal value, you need to use the hashref syntax, instead of the string syntax : my $result = $sqla->join( 'table', { operator => '=>', condition => { '%1$s.table_id' => {-ident => '%2$s.table_id'}, '%2$s.date' => {'>' => {-ident => '%1$s.date'}}, '%2$s.event_id' => 1}}, 'table_log' ); Agreed, this is a bit cumbersome, but I couldn't think of anything better, and usually the joins are produced automatically by the ORM layer. Through your question I realised that SQL::Abstract::More documentation is not up to date; what you get in $result is not ($sql, @bind), but rather a hashref with keys C<sql>, C<bind>, and other pieces of information; I'll fix that doc in the next release. Thanks for your interest in SQLAM, Laurent Dami

Wed May 01 13:09:52 2013 The RT System itself - Status changed from 'new' to 'open'

Wed May 01 13:16:52 2013 DAMI [...] cpan.org - Correspondence added

Le Mer 01 Mai 2013 12:31:00, XENO a écrit : Show quoted text

> http://stackoverflow.com/q/16307208/206466 > > I'm trying to do a join using SQL::Abstract::More that has an `and and > then a literal value, not on a table column.

This was a question, not a bug, so I reject the ticket.

Wed May 01 13:16:52 2013 DAMI [...] cpan.org - Status changed from 'open' to 'rejected'