[CDBI] Re: Searching using a might_have relationship.

Oliver Jeeves wrote::
> Basically, I want to use a value from a might_have relationship to
> search, and I don't want to have to do multiple SQL queries to do it.
> Is there an easy way to do this?

No, I don't believe so. Support for might_have relationships could be better
in CDBI. I think it's a weakness anyway. They are harder to populate than a
has_many() and you cannot easily search() for them via the parent class.

You have a couple of options, I think.

(1) Instead of

 MyDB::A->search(other => 'foo');

    you can use B's search() method and then MyDB::A->retrieve() the IDs
    that you found in B, like so:

 my @others = map { MyDB::A->retrieve($_->id) } MyDB::B->search(other => 'foo');

    Relatively easy, but not very efficient.

(2) You could use set_sql(), like so:

 MyDB::A->set_sql('others' => <<"");
SELECT __ESSENTIAL(a)__
FROM   __TABLE(MyDB::A=a)__,
       __TABLE(MyDB::B=b)__,
WHERE  __JOIN(a b)__
AND    b.other = ?

 MyDB::A->search_others('foo');

    Refer to http://wiki.class-dbi.com/wiki/Using_joins for more info.

(3) You could use Class::DBI::Plugin::DeepAbstractSearch. Not sure if it
    supports might_have's or not, though I suspect it does.

http://search.cpan.org/~sriha/Class-DBI-Plugin-DeepAbstractSearch/DeepAbstractSearch.pm

Hope this helps,
Ed