[Catalyst] Catalyst / DBIx Class Relationship many_to_many problem
Hello! I've started using Catalyst, and now I've started writing my second app (after 1st one - MyApp from tutorial) I've created db tables analogic to tutorial: courses (id, name) users (id, name, email, etc.) course_users (course_id, user_id) When trying to list all users from one course, in my tt view list.tt2 I do: [% FOREACH course IN courses %] [% tt_users = [ ]; tt_users.push(user.name) FOREACH user = course.users %] [% END -%] And then I get error: Couldn't render template undef error - DBIx::Class::Relationship::ManyToMany::__ANON__(): DBI Exception: DBD::Pg::st execute failed: ERROR: syntax error at or near . LINE 1: SELECT user.id, user.name, user.email, user.pass, user.role ... ^ [for Statement SELECT user.id, user.name, user.email, user.pass, user.role FROM course_users me JOIN users user ON ( user.id = me.user_id ) WHERE ( me.course_id = ? ) with ParamValues: 1='1'] at /home/jtutaj/workspace/saps/root/src/courses/list.tt2 line 29 From message I can see there's something with many_to_many relationship, but found nothing when comparing to working tutorial MyApp application. My Schema relationship parts are: Courses.pm: __PACKAGE__-has_many(course_users = 'saps::Schema::CourseUsers', 'course_id'); __PACKAGE__-many_to_many(users = 'course_users', 'user'); Users.pm: __PACKAGE__-has_many(course_user = 'saps::Schema::CourseUsers', 'user_id'); __PACKAGE__-many_to_many(courses = 'course_user', 'course'); CourseUsers.pm: __PACKAGE__-belongs_to(course = 'saps::Schema::Courses', 'course_id'); __PACKAGE__-belongs_to(user = 'saps::Schema::Users', 'user_id'); I already tried getting just simple data like user.name / course.name / course_user.user_id and it all works fine, but when FOREACH user = course.users error always occurs. Any help? Regards Jakub Tutaj -- View this message in context: http://www.nabble.com/Catalyst---DBIx-Class-Relationship-many_to_many-problem-tp21775313p21775313.html Sent from the Catalyst Web Framework mailing list archive at Nabble.com. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Catalyst / DBIx Class Relationship many_to_many problem
J. Shirley wrote: This is actually a DBIx::Class issue and not Catalyst, but the answer to your problem is to simply enable quoting. Since 'user' is a reserved word, you must quote it. See this section in the DBIx::Class cookbook: http://search.cpan.org/~ash/DBIx-Class-0.08010/lib/DBIx/Class/Manual/Cookbook.pod#Setting_quoting_for_the_generated_SQL. That works perfectly! A lot of thanks for fast support. Jakub Tutaj -- View this message in context: http://www.nabble.com/Catalyst---DBIx-Class-Relationship-many_to_many-problem-tp21775313p21797190.html Sent from the Catalyst Web Framework mailing list archive at Nabble.com. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
[Catalyst] LEFT JOIN with AND statement
Hello!
I'd like to prepare query like:
SELECT group_tasks.*,student_tasks.*
FROM group_tasks
LEFT JOIN student_tasks
ON group_tasks.id = student_tasks.group_task_id AND student_tasks.user_id =
1
WHERE group_tasks.group_id = 1
I stuck at this point:
$c-stash-{studentgrouptasks} = [$c-model('sapsDB::GroupTasks')-search(
{
'me.group_id' = 1,
},
{
join = [qw/ studenttasks/]
}
)];
I don't know where can I put 'AND student_tasks.user_id = 1' so it's
corresponding with 'join'. I'd like to have list of all grouptasks designed
for the student and if student already did the task (there's a studenttask
record in DB) I also want info about it. That's why I need this 'AND' in
JOIN .. ON statement, instead of normal JOIN .. ON .. WHERE clause. Any
help?
My tables are something like this:
GroupTasks:
id
status
task_id
group_id
StudentTasks:
id
status
group_task_id
user_id
has_many and belongs_to :
saps::Schema::GroupTasks-has_many(studenttasks =
'saps::Schema::StudentTasks', 'group_task_id');
saps::Schema::StudentTasks-belongs_to(grouptask =
'saps::Schema::GroupTasks', 'group_task_id');
Another question is, if I have proper query, how to distinct
studenttasks.status and grouptasks.status using stashed 'studentgrouptasks'
variable in TT (using FOREACH etc.)?
Regards.
Jakub Tutaj
--
View this message in context:
http://www.nabble.com/LEFT-JOIN-with-AND-statement-tp22567550p22567550.html
Sent from the Catalyst Web Framework mailing list archive at Nabble.com.
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] LEFT JOIN with AND statement
Zbigniew Lukasiak wrote:
but answering
your question I think the only way to do it is via a custom ResultSet
(http://search.cpan.org/~ribasushi/DBIx-Class-0.08099_07/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource)
Hi!
I tried the Cookbook way, but Catalyst couldn't find register_extra_source
function...so from there I searched for that problem and found this link:
http://www.perlmonks.org/?node_id=633800
Basically, I've created file GroupTasksComplex and put it into Schema
folder. It looks like this:
package saps::Schema::GroupTasksComplex;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__-load_components(Core);
__PACKAGE__-table(group_tasks); #this probably could be any name
__PACKAGE__-add_columns(
qw/
gt_id task_name gt_status gt_opentime gt_closetime gt_task_id gt_groupid
id compilationstatus runstatus originality comment registered codesize
memoryusage group_task_id user_id
/
); # list of column names you want to get in return
my $source = __PACKAGE__-result_source_instance()-name( \SQL);
(
SELECT group_tasks.id as gt_id, tasks.name as task_name, group_tasks.status
as gt_status, group_tasks.opentime as gt_opentime, group_tasks.closetime as
gt_closetime, group_tasks.task_id as gt_task_id, group_tasks.group_id as
gt_groupid, student_tasks.*
FROM group_tasks
LEFT JOIN student_tasks
ON group_tasks.id = student_tasks.group_task_id AND student_tasks.user_id =
? LEFT JOIN tasks ON group_tasks.task_id = tasks.id
WHERE group_tasks.group_id = ? )
SQL
Then to get query in my controller I do:
my $studentgrouptasks = $c-stash-{studentgrouptasks} =
[$c-model('sapsDB::GroupTasksComplex')-search (
{},
{
bind = [ $c-user-id,$groupId ]
}
) ];
I hope that will help someone else too.
Thanks Zbigniew for a hint!
Regards!
Jakub Tutaj
--
View this message in context:
http://www.nabble.com/LEFT-JOIN-with-AND-statement-tp22567550p22686014.html
Sent from the Catalyst Web Framework mailing list archive at Nabble.com.
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
[Catalyst] How to bind an array using my own schema
Hi!
Is it possible to bind an array in code like below? Binding simple variables
works perfectly...
@myArray = (1,2,3);
my $superSchema = [$c-model('myApp::mySuperSchema')-search (
{},
{
bind = [ @myArray, $number]
}
) ];
I'm using query like that in my schema mySuperSchema.pm:
SELECT column_id, number FROM table
WHERE column_id IN (?) AND number != ?
Now I get an error:
Cannot bind unknown placeholder 3
So is it matter of '?' in SQL query or I should specify bind parameter in
different way?
Regards
Jakub Tutaj
--
View this message in context:
http://www.nabble.com/How-to-bind-an-array-using-my-own-schema-tp23526664p23526664.html
Sent from the Catalyst Web Framework mailing list archive at Nabble.com.
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
