Re: [Dbix-class] additional condition in LEFT JOIN in DBIx::Class
On Thursday, 17 of September 2009 18:18:44 Emanuele Zeppieri wrote:
Peter Rabbitson wrote:
Eden Cardim wrote:
On Thu, Sep 17, 2009 at 9:40 AM, Paweł Pabian pawel.pab...@implix.com
wrote:
Hi
I have some users
package Schema::User;
__PACKAGE__-table('users');
__PACKAGE__-add_columns('id', 'login');
__PACKAGE__-set_primary_key('id');
__PACKAGE__-has_many('attributes' = 'Schema::Attribute', 'users_id');
and they have many attributes
package Schema::Atrribute;
__PACKAGE__-table('attributes');
__PACKAGE__-add_columns('users_id', 'name', 'value');
__PACKAGE__-set_primary_key('users_id', 'name');
__PACKAGE__-belongs_to('user' = 'Schema::User', 'users_id');
Now i want to find Users that don't have Attribute of given name.
In raw SQL it needs name condition to be placed in LEFT JOIN:
SELECT *
FROM users AS u
LEFT JOIN attributes AS a
ON u.id=a.users_id
AND a.name=car
WHERE a.users_id IS NULL
How to force DBIx::Class to add this
AND a.name=car
part to join condition?
DBIC doesn't support variable join conditions, but you can add the AND
a.name = 'car' condition to your where clause to the same effect.
He can not. A left with right-side condition join is not the same as
left join + where condition.
The only way to do this currently is with a virtual view:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08111/lib/DBIx/Class/Resul
tSource/View.pm
Wouldn't a subquery like this work with the latest DBIC?
my $users_wo_car = $schema-resultset('User')-search({
id = {
'not in' = $schema-resultset('Attribute')-search({
name = 'car'
})-get_column('users_id')-as_query
}
});
(Untested, just noted that there are similar subqueries in from_subquery.t)
Yes.
It gives correct result but In my case it's ~900 times slower than JOIN due to
DEPENDENT SUBQUERY optimizer bug in MySQL 5.x. That's why i asked question
about JOIN version.
Thanks.
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
[Dbix-class] Filtering with Many-to-Many relationship
Alright, it seemed to me that what I wanted to do was straightforward, but
it doesn't seem like I can find any resources on how to do it... so maybe
I'm just going about it the wrong way.
I have a table Products and a table Tags with a many_to_many relationship
setup, the purpose of which would be to easily generate a resultset of
products with the given tag(s). However, I'm running in to problems when I
want to generate a resultset of product records filtered with multiple
tags. I was thinking that one of the following would work:
my $tag1 = 'A';
my $tag2 = 'B';
$c-model('MyApp:Tag')-single({ tag_id = $tag1 })-products-search({ tags
= { tag_id = $tag2 }});
OR
$c-model('MyApp:Tag')-single({ tag_id = $tag1
})-products-search_related('product_tags', { tag_id = $tag2 });
But apparently the many_to_many relationship accessor only returns a list of
row_objects rather than a resultset itself, so I can't perform resultset
searches on that. So I tried the following to chain together two searches:
$c-model('MyApp:Product')-search_related('product_tags',{ tag_id = $tag1
})-search_related('product_tags', { tag_id = $tag2 });
I tried the same chaining using search and joins to link to the
ProductTagsLink table, they combined the searches so that it was joining on
the related table searching for tag_id = $tag1 AND tag_id = $tag2 so that it
wasn't returning anything. I was looking in to creating a subquery for
this, but my attempts have failed and the documentation mentions that
subqueries are experimental. I'm thinking if I had an accessor to access
the many_to_many resultset as an actual resultset rather than a list of
row_objects I could probably get it working, but I didn't see any
documentation on actually doing that. I managed to get the actual concept
working using this code here, but would prefer a straight SQL/DBIX solution
since this doesn't seem very efficient:
my $tag1 = 'A';
my $tag2 = 'B';
my $tag_rs = $c-model('ReaneyDesignDB::Tag')-single({ tag_id = $tag });
my @products = $tag_rs-products;
$c-stash-{products} = [grep { ($_-search_related('product_tags', { tag_id
= $tag2 })-single) } @products];
Anyone have experience trying to accomplish what I'm doing here? Let me
know if I'm missing something here. Thanks.
- Shawn Marincas
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Filtering with Many-to-Many relationship
On Fri, Sep 18, 2009 at 3:06 PM, Shawn Marincas
shawngmarin...@gmail.com wrote:
I have a table Products and a table Tags with a many_to_many relationship
setup, the purpose of which would be to easily generate a resultset of
products with the given tag(s). However, I'm running in to problems when I
want to generate a resultset of product records filtered with multiple
tags. I was thinking that one of the following would work:
my $tag1 = 'A';
my $tag2 = 'B';
my $product_rs = $schema-resultset('Product')-search_rs({
'tag.tag_id' = { -in = [qw/A B/] }
}, {
join = { 'product_tags' = 'tag' }
});
But apparently the many_to_many relationship accessor only returns a list of
row_objects rather than a resultset itself, so I can't perform resultset
searches on that. So I tried the following to chain together two searches:
You've used the -search method in your code in list context, so it
calls -all over the resultset and returns a list of row objects.
--
wallace reis/wreis Catalyst and DBIx::Class consultancy with a clue
Software Engineer and a commit bit:
http://shadowcat.co.uk/catalyst/
Shadowcat Systems Limited
http://www.shadowcat.co.uk http://www.linkedin.com/in/wallacereis
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
