Re: [Dbix-class] relationship with additional constraints?
On 17/05/18 22:25, Matt S Trout wrote: > Surely http://p3rl.org/DBIx::Class::ParameterizedJoinHack is exactly > what's needed here - just use it on a belongs_to rel with a join_type of > left so non-matching entries are still returned, then prefetch that rel? > > (ironic since I'm replying to the man who was kind enough to sponsor Shadowcat > to develop that module in the first place :) > Yes, I tried it yesterday and it is (almost) exactly what I need, thank you! I say "almost" because my use case actually calls for a might_have relationship (rather than has_many) but it is easy enough to ->first off the entry in the rs. Stuart ___ 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] relationship with additional constraints?
Hello all,
Is it possible to create a relationship which has, along with the join
condition, an additional constraint where the value could somehow be passed
when the search() method is called?
What I would like to achieve (in sql) is this:
(it's a contrived example, but the structure matches my real life project
almost exactly...just with less abstract names)
SELECT movies.name, reviews.text
FROM movies
LEFT JOIN reviews ON movies.id = reviews.movie_id AND reviews.person = ?; 'Sue'
This query would satisfy each of the following cases:
- Get a list of all movies, as well as a review by Sue if it exists
- If a movie does not have a review by Sue then it should still be returned
with reviews.text = NULL.
- If a movie does not have a review by Sue but it has a review by someone else
then it should also be returned with reviews.text = NULL
The closest I can get is the following code, but it only satisfies the first
two cases:
$db->resultset('Movie')->search(
{ 'reviews.person' => [undef, 'Sue'] },
{ prefetch => 'reviews' },
);
which puts the constraint into the WHERE (as normal)...
SELECT movies.name, reviews.text
FROM movies
LEFT JOIN reviews ON movies.id = reviews.movie_id
WHERE reviews.person = NULL
OR reviews.person = ?; 'Sue'
But this will not return any movies which don't have a review by Sue and have a
review by someone else. Which is why the constraint really needs to be in along
with the LEFT JOIN condition.
Any help with this would be greatly appreciated,
Stuart
___
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] has_one over joining table
On 29/01/18 17:31, Andrew Beverley wrote:
> Something like (in Result::Town):
>
> __PACKAGE__->might_have(
> "town_people_mayor",
> "MyApp::Schema::Result::TownPeople",
> sub {
> my $args = shift;
> return {
> "$args->{foreign_alias}.town_id" => { -ident =>
> "$args->{self_alias}.id" },
> "$args->{foreign_alias}.is_mayor" => 1,
> };
> }
> );
>
> You would then do something like:
>
> $town->town_people_mayor->mayor
Hi Andy,
Yes, that works perfectly...thank you!
It would have been nice to do it in one step as I wouldn't need to
change the calling side...but I blame myself for coming up with a shabby
db structure in the first place ;)
Thanks again for your help,
Stuart
___
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] has_one over joining table
Hi,
I have a many to many relationship over a joining table, eg.
towns -> town_people -> people
In some cases it is possible for there to be has_one relationships
between the two outer tables. It's a bit of a contrived example but lets
say a person can live in multiple towns but only one is her home town.
Another (probably better) example would be that every town has one mayor.
I currently have a home_town_id field in the people table, which makes
things difficult with the foreign key constraint when inserting new
people with new home towns. And if there is a mayor_id in the towns
table which links to people, it is impossible to insert any new data
without disabling the foreign key constraints first.
So, my idea is (please say if this is also not such a good solution) to
remove the home_town_id field from people and add an is_home_town
NULLable flag (with unique index) to town_people. And similarly an
is_mayor flag to the same table...this way all town/people related data
is in one table and I no longer need to worry about foreign key
constraints when inserting the outer tables.
And finally, the DBIC question
Is there a way to set up a has_one (home_town/mayor) relationship
between the people and towns tables with the following constraint on the
joining table: is_home_id = 1
So that I can make calls like this:
$person->home_town;
$town->mayor;
Or prefetch the related data in searches like this:
$resultset('People')->search(
{ 'me.name' => 'Sue' },
{ prefetch => 'home_town' },
);
Many thanks,
Stuart
___
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] Bug in resultset?
On Wed, 2010-07-07 at 10:18 +0100, Chris Cole wrote:
I'm finding when restricting a search on the same column for multiple
criteria only one of them is being applied. e.g.
my $rs = $self-resultset('NgsMappings')-search(
{
'mp_start' = {'=', $start},
'mp_start' = {'', $end},
'rs_name' = $chr,
'me_sample' = {'like', $dataset},
},
{
columns = [qw/mp_strand mp_start mp_end mp_freq/],
join = [qw/mp_me_id mp_ref_id/],
}
);
Gives this SQL (note the single use of 'mp_start'):
SELECT me.mp_strand, me.mp_start, me.mp_end, me.mp_freq
FROM ngs_mappings me
JOIN ngs_map_exps mp_me_id ON mp_me_id.me_id = me.mp_me_id
JOIN ngs_ref_seqs mp_ref_id ON mp_ref_id.rs_id = me.mp_ref_id
WHERE ( ( me_sample LIKE ? AND mp_start ? AND rs_name = ? ) ) :
'FPAox%', '5446714', 'chr1'
I can't find anything in the documentation regarding this, so is it a
bug or am I missing something (probably)?
no, no bugits a hash ref you are sending to the search so when you
use mp_start twice like that one of the values will be overwriting the
other.
to search twice on the same field you could try something like this:
{
mp_start = { '=', $start, '', $end, },
}
Stuart.
--
==
united-domains AG - The Domain People.
Gautinger Strasse 10
D-82319 Starnberg
Tel. + 49 (0) 81 51 / 3 68 67 - 33
Fax + 49 (0) 81 51 / 3 68 67 - 77
http://www.united-domains.de
--
Sitz: Starnberg, HRB 127020 (AG München)
Vorstand: Florian Huber (Vors.)
Alexander Helm, Markus Eggensperger
Aufsichtsrat: Norbert Lang (Vors.)
==
signature.asc
Description: This is a digitally signed message part
___
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] [Fwd: Re: [Catalyst] db delete]
This message is being forwarded to this mailing list from the Catalyst
mailing list as i was told it had more relevance here...does anybody
have any ideas?
Hi,
I recently updated all the Catalyst and DBIC modules on my system and
afterwards i started having problems performing deletes in the database
with Catalyst. I'll go straight ahead and give you the error message:
Caught exception in Project::Controller::TldMethods-delete Not a HASH
reference at .../perl5/Class/Accessor/Grouped.pm line 284.
this happens after doing the following:
my $object = $c-model('DB::Object')-find($id);
$c-log-debug(ref $object); # Project::Model::DB::Object
$object-delete;
however...i have isolated that this must be something to do with the
Catalyst Model as i dont have any trouble deleting when i just use
straight DBIC:
my $schema = DB-connect( $dsn, $user, $pass, );
my $object = $schema-resultset('Object')-find($id);
print ref($object); # DB::Object
$object-delete;
...this works properly.
Furthermore, this problem only happens with the delete command on the db
object in Catalyst...all other accessor/update/create methods work fine.
(sorry if this problem has already been posted but i only joined the
mailing list today)
Many thanks,
Stuart.
Forwarded Message
From: Tomas Doran bobtf...@bobtfish.net
To: do...@united-domains.de, The elegant MVC web framework
catal...@lists.scsys.co.uk
Subject: Re: [Catalyst] db delete
Date: Wed, 12 May 2010 18:44:20 +0200
On 12 May 2010, at 17:12, Stuart Dodds wrote:
This is more of a Catalyst/DBIx problem and i've probably got it wrong
and it should be in the DBIx mailing list, but i know most of you guys
here use both, so hopefully someone can help me out.
Yes, this should be on the DBIC mailing list, sorry.
Also, you mean DBIC, not DBIx (which is a namespace for DBI
eXtensions, containing many many projects).
Cheers
t0m
--
==
united-domains AG - The Domain People.
Gautinger Strasse 10
D-82319 Starnberg
Tel. + 49 (0) 81 51 / 3 68 67 - 33
Fax + 49 (0) 81 51 / 3 68 67 - 77
http://www.united-domains.de
--
Sitz: Starnberg, HRB 127020 (AG München)
Vorstand: Florian Huber (Vors.)
Alexander Helm, Markus Eggensperger
Aufsichtsrat: Norbert Lang (Vors.)
==
signature.asc
Description: This is a digitally signed message part
___
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] SQL::Abstract regression?
I had the exact same problem today.the only way I could fix it was
to remove the prefetch from the search.
-Original Message-
From: Paul Makepeace pa...@paulm.com
Reply-to: DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
To: DBIx::Class user and developer list dbix-class@lists.scsys.co.uk
Subject: [Dbix-class] SQL::Abstract regression?
Date: Mon, 13 Jul 2009 10:52:24 -0500
I have,
__PACKAGE__-has_many(publication_story = 'IDL::Schema::Story',
'publication_uid');
__PACKAGE__-belongs_to(publication_type = 'IDL::Schema::CodeTree',
'publication_type_uid', { join_type = 'left'});
my @SEARCH_WITH_STORY_COUNT_ARGS = (
join = [qw/publication_story/],
select = [{ count = 'publication_story.uid' }, qw/me.uid me.name
me.short_name
me.url me.description me.publication_type_uid me.region_uid
me.channel_type_uid
me.circulation me.ave1 me.ave2 me.comments/,],
as = [qw/story_count uid name short_name url description publication_type_uid
region_uid channel_type_uid circulation ave1 ave2 comments/],
group_by = [qw/me.uid/],
prefetch = [qw/publication_type region/],
);
and this is generating this wrong-looking SQL,
DBIx::Class::ResultSet::search(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'publication_story.uid' in 'field list'
[for Statement SELECT me.story_count, me.uid, me.name, me.short_name,
me.url, me.description, me.publication_type_uid, me.region_uid,
me.channel_type_uid, me.circulation, me.ave1, me.ave2, me.comments,
publication_type.uid, publication_type.parent_uid,
publication_type.value, publication_type.hide, publication_type.alias,
publication_type.order_priority, publication_type.show_in_lists,
publication_type.data_type, publication_type.cascade_data_type,
publication_type.description, region.uid, region.parent_uid,
region.value, region.hide, region.alias, region.order_priority,
region.show_in_lists, region.data_type, region.cascade_data_type,
region.description FROM (SELECT COUNT( publication_story.uid ) AS
story_count, me.uid, me.name, me.short_name, me.url, me.description,
me.publication_type_uid, me.region_uid, me.channel_type_uid,
me.circulation, me.ave1, me.ave2, me.comments FROM publication me
WHERE ( ( name LIKE ? OR short_name LIKE ? ) ) GROUP BY me.uid ORDER
BY name) me LEFT JOIN story publication_story ON
publication_story.publication_uid = me.uid LEFT JOIN code_tree
publication_type ON publication_type.uid = me.publication_type_uid
LEFT JOIN code_tree region ON region.uid = me.region_uid WHERE ( (
name LIKE ? OR short_name LIKE ? ) ) ORDER BY name
specifically, FROM (SELECT COUNT( publication_story.uid ) AS
story_count, ... looks wrong. Or should I update my code somehow?
This error coincides with upgrading to latest DBIx::Class today.
Paul
___
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
___
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] search NOT IN
Is it possible to do a search on a field where it is not equal to a list
of values.
For example, the sql I would like to produce is as follows:
WHERE id NOT IN (?,?,?)
however doing:
-search({
id = { '!=', \...@id_list }
});
doesn't do the right thing as you get:
WHERE id != ? OR id != ? OR id != ?
the only other way I can think is to do a search_literal with some
string concatenation.
Any help would be great,
Stuart.
___
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] Re: search NOT IN
On Mon, Jul 06, 2009 at 04:26:27PM +0200, Stuart Dodds wrote: For example, the sql I would like to produce is as follows: WHERE id NOT IN (?,?,?) Assuming you use a recent version of SQL::Abstract, see: http://search.cpan.org/~ribasushi/SQL-Abstract-1.56/lib/SQL/Abstract.pm#Special_operators_:_IN,_BETWEEN,_etc. Tom Ahh that's where it's hidingthanks for your help. Stuart. ___ 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 ___ 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
