[CDBI] Question from newbie - CDBI search/search_like

Peter Speltz peterspeltz at gmail.com
Wed Nov 30 23:22:20 GMT 2005


On 11/30/05, Vitaliy Babiy <vitaliy.babiy at gmail.com> wrote:
> Hello Guys
>
> I'm migrating my old code to CDBI and I'm having some difficulties
> because it's my first experience with CDBI.
>
> I have 2 SQL query which I need to move to CDBI.
>

(quick read of SQL semantics)

> 1) SELECT a.id, a.name, al.id, al.name, al.cnt_tracks, al.status
>    FROM artist AS a INNER JOIN album AS al ON a.id=al.artist
>    WHERE al.name like '%$query%' ORDER BY a.name, al.name LIMIT 20

> 2) SELECT a.id, a.name, al.id, al.name, al.cnt_tracks, al.status
>    FROM artist AS a INNER JOIN album AS al ON a.id=al.artist
>    ORDER BY al.updated DESC LIMIT 50
>

It looks to me  that these queries  just do  simple searches of the 
album query where artist IS NOT  NULL. and what ever other parameters
assuming you are not trying to exclude albums that have artist id's
that refer to the database.  Is that right?

If this is the case  you could use Class:;DBI:;Sweet, or
Class:;DBI:;AbstractSearch with their respective "search" and
"search_where"  routines where you can specify operators .

Or you could use CDBI's retrieve _from_sql   -- from docs;

On occasions where you want to execute arbitrary SQL, but don't want
to go to the trouble of setting up a constructor method, you can
inline the entire WHERE clause, and just get the objects back
directly:

  my @cds = Music::CD->retrieve_from_sql(qq{
    artist = 'Ozzy Osbourne' AND
    title like "%Crazy"      AND
    year <= 1986
    ORDER BY year
    LIMIT 2,3
  });


Now, if you really do need to do a join , then Class;:DBI::Sweet's 
"search" can do it or you can write your own set_sql.

HTH,



--
pjs




More information about the ClassDBI mailing list