Feature request - Allow searching columns of related tables in search() and search_like()

[prev] [thread] [next] [Date index for 2005/02/22]

Thanks Matt!
Can you please write a real set_sql line using the Music:CD example?
I can't figure out what I have to replace the 'Join::To::This' and 'table=
 this that' with.

-Alex


-----Urspr=FCngliche Nachricht-----
Von: Matt S Trout [mailto:cdbi-talk@xxxxx.xx.xx]=20
Gesendet: Freitag, 18. Februar 2005 00:13
An: Hartmaier Alexander
Cc: cdbi-talk@xxxxxx.xxxxx.xxx
Betreff: Re: Feature request - Allow searching columns of related tables =
in search() and search_like()

On Thu, Feb 17, 2005 at 09:21:05PM +0100, Hartmaier Alexander wrote:
> Any news on this?
> I'd need searching and ordering by fields of foreign tables all the tim=
e!
>=20
> I didn't look at the CDBI internals but wouldn't it be the easiest way =
to create an sql query which just returns all matching primary id's of th=
e searched object and then retrieve all ESSENTIAL columns?
>=20
> The 'order by' can be done the same way...

It can, but it's not an amazingly efficient one. One alternative is to do=

something like

__PACKAGE__->set_sql(Retrieve =3D> <<EOSQL);
SELECT __ESSENTIAL__
FROM __TABLE__, __TABLE__(Join::To::This), __TABLE(Join::To::That)
WHERE __JOIN__(table this that)
AND %s
EOSQL

and then you should be able to do

Class::Name->search( 'table.foo' =3D> 1, 'this.bar' =3D> 'Spoo',
                        { order_by =3D> 'that.baz' });

and it'll all work fine.

I've been thinking about packaging this up as a general case, but am unsu=
re
whether it should be a plugin and what syntax to use if I do. Suggestions=
?

--=20
    Matt S Trout            Brag sheet:    http://trout.me.uk/services.ht=
ml
LAMP, Infrastructure        Contact:       services@xxxxx.xx.xx
   and Automation
     specialist                                       Do it once. Do it r=
ight.

*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*"*
Hinweis: Dieses E-mail kann vertrauliche und gesch=FCtzte Informationen e=
nthalten.
Sollten Sie nicht der beabsichtigte Empf=E4nger sein, verst=E4ndigen Sie =
bitte den Absender und l=F6schen Sie dieses E-mail dann sofort.

Notice: This e-mail contains information that is confidential and may be =
privileged.
If you are not the intended recipient, please notify the sender and then =
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*"*