Re: Trouble making set_sql work...

[prev] [thread] [next] [Date index for 2004/07/13]

From: John Beppu
Subject: Re: Trouble making set_sql work...
Date: 16:46 on 13 Jul 2004

[  date  ] 2004/07/13 | Tuesday | 12:36 PM
[ author ] Andy Harrison <aharrison@xxxxx.xxx> 

> On Tue, 13 Jul 2004 11:04:54 -0400, Jesse Sheidlower <jester@xxxxx.xxx> wrote:
> > I think you're using the ' symbol incorrectly--I've forgotten
> > the exact form of your original query but if you're single-quoting
> > the ? in the "WHERE option =" line, then it's a question mark,
> > not a placeholder. Try removing the single quotes.
> 
> Thanx everyone.  I've been able to get this functioning now.
> 
> However, Colm is right, you can't do 'LIMIT ?'.
> 
> Could anyone offer suggestion on how I could do this?  I was just
> hoping to make it dynamic so that I could easily generate a top
> 10-20-100-whatever list.

Instead of using a ?, use a %s .

Example:

    __PACKAGE__->set_sql(latest => qq{
	     SELECT c.title,
		    a.id,
		    max(i.created_date) as latest
	       FROM art a, art_collection c, image i
	      WHERE a.collection_id = c.id
		AND a.image_id = i.id
	   GROUP BY c.title
	   ORDER BY latest DESC
	      LIMIT %s
    });

    # and then later on in the code...

    $limit = 10; # 20, 100, or whatever

    my $sth = $class->sql_latest($limit);