[Dbix-class] Searching based on a Result Class Method

[Rob Kinyon]

On Tue, Apr 6, 2010 at 05:11, Hetényi Csaba <csabiwork at tata.hu> wrote:
> Thank You Rob for help.
> How can i create a clause/resultset to get only that users, which has
> a photo?

One easy (and probably sufficient for your needs) solution is:

my $users_with_photos_rs = $schema->class('User')->search(
    { 'photos.user_id' => \'IS NOT NULL' },
    { join => 'photos' },
);

That will create SQL that looks like:

SELECT me.* FROM users me LEFT OUTER JOIN photos photos ON (me.id =
photos.user_id) WHERE ( photos.user_id IS NOT NULL );

Not ideal SQL, but still very functional.

Another solution is to create a second relationship between users and
photos with join_type => 'inner'. A has_many relationship has
join_type => 'left' (which is normally what you want). But, in this
case, you want an inner relationship. So, you'd create a -SECOND-
relationship between users and photos that looks something like:

__PACKAGE__->has_many(photos_inner =>
'Tarsv1::Schema::Result::Photos', 'user_id', { join_type => 'inner'
});

Then, you do something like:

my $users_with_photos_rs = $schema->class('User')->search(
    {}, # Yes, no search clause - this is your WHERE clause and we
don't need one.
    { join => 'photos_inner' },
);

That will create SQL that looks something like:

SELECT me.* FROM users me JOIN photos photos ON (me.id = photos.user_id);

which is exactly what you want.

I would recommend the first usage - no need to create a second
relationship when the additional clause in the search works just fine.

Rob