[Dbix-class] search_literal

[brett gardner]

Well

Is there anyway to do the following.

I have a Respondent record, which has a one to many relationship with 
Answer records.

I want to be able to get a set of Respondents who answered a particular 
way and I first tried do it like

my $rs = $schema->resultset('Respondent')->search(
	{
		'answers.question_id'=>$foo,
		'answers.answer'=>$bar
	},
	{
		join=>'answers',
	}
);


Which works, but when you call search on this resultset again, ie, 
searching withing that subset, it fails eg

$rs = $rs->search(
	{
		'answers.question_id'=>$foo_1,
		'answers.answer'=>$bar_1,
	},{
		join=>'answers',
	}
);


The resulting SQL does not join "answers" twice and puts in the where 
clause

answers.question_id=$foo AND answers.question_id = $foo_1 AND answers.answer = $bar AND answers.answer = $bar_1


Which is incorrect.

I can only do with with an EXISTS subquery clause and this is not 
available yet in SQL::Abstract (as far as I know) and I have to embed 
the values directly into the SQL using search_literal as I cannot use 
placeholders.

So is there anyway around this conundrum?

Matt S Trout wrote:
> On Fri, Sep 21, 2007 at 09:20:16AM +1000, brett gardner wrote:
>   
>> Matt S Trout wrote:
>>     
>>> On Thu, Sep 20, 2007 at 08:43:20AM +0100, Ash Berlin wrote:
>>>  
>>>       
>>>> brett gardner wrote:
>>>>    
>>>>         
>>>>> I think I have found a bug with search_literal. If you get a resultset 
>>>>> that came from a search_literal, and try to apply another search_literal 
>>>>> to it, the bind values are in the wrong order eg.
>>>>>
>>>>> my $rs = $schema->resultset('Foo')->search_literal('foo = ?', 1);
>>>>> $rs = $rs->search_literal('bar = ?',2);
>>>>>
>>>>> Spits out the sql
>>>>>
>>>>> SELECT foo.field
>>>>>           
>>>> >FROM foo
>>>>         
>>>>> WHERE ( ( foo.bar = ? AND foo.foo = ? ) )
>>>>>
>>>>> With a bind values array of "[1,2]". It should be a bind values array of 
>>>>> "[2,1]".
>>>>>
>>>>> It seems to be adding the second literal string to the front of the 
>>>>> where clause, but adding the values to the end of the array.
>>>>>
>>>>> I'm using 0.08003 but a quick search in the change log doesn't mention 
>>>>> anything about search_literal
>>>>>
>>>>> Cheers,
>>>>> Brett Gardner
>>>>>
>>>>>      
>>>>>           
>>>> Sounds like a bug to me - can you please whip up a patch that gives us a
>>>> failing test case?
>>>>    
>>>>         
>>> Plus a doc patch saying "this only exists for Class::DBI compatibility, do
>>> -not- use in normal DBIx::Class code".
>>>
>>>  
>>>       
>> Do you mean do not use "search_literal" at all in DBIx::Class code?
>>     
>
> Correct.
>
> There's never any reason to and it doesn't chain properly (as you've just
> noticed ...)
>
>