CODEWITHSUNDEEP
perlcatalyst
Grigor
Grigor

Reputation: 4059

Perl Catalyst: Resultset and Relations

I have two tables in my database and one of the tables is associated with my Accounts table.

So in my Schema Result for Account.pm I added the following line.

__PACKAGE__->has_many('subjects', 'MyApp::DBIC::Schema::Subject', {'foreight.account_id' => 'self.account_id'});

Then in my controller I make a search like this.

$c->stash->{search_results} = $c->model('DB::Account')->search(
{ -or => [
   firstname => {like => '%'.$search_term.'%'},
   'subjects.subject_title' => {like => '%'.$search_term.'%'},
 ]
},
{
 join => 'subjects',
 rows => '3',
},
{
 order_by => 'first name ASC',
 page => 1,
 rows => 10,
}
);

It does not output any errors, but I can't figure out how to output the results on my view file. Is this a correct method of making relations between two tables?

My goal: provided a search_term, search two tables and output the result in view file. My SQL would look something like this: 

SELECT FROM Accounts,Subjects WHERE Accounts.firstname=$search_term OR Subjects.subject_title=$search_term LEFT JOIN Subjects ON Accounts.account_id=Subject.account_id

And would want to output the result in view file, as I stated above.

I am fairly new to Perl and some of the documentations don't make that much sense to me, still. So any help and tips are appreciated.

Upvotes: 1

Views: 1327

Answers (2)

Max
Max

Reputation: 96

You have error in your query:

Try:

$c->stash->{search_results} = $c->model('DB::Account')->search(
  { -or => [
      firstname => {like => '%'.$search_term.'%'},
      'subjects.subject_title' => {like => '%'.$search_term.'%'},
    ]
  },
  {
    join => 'subjects',
    order_by => 'firstname ASC',
    page => 1,
    rows => 10,
  }
);

Upvotes: 2

robert_b_clarke
robert_b_clarke

Reputation: 1513

The join looks OK to me, but it would make sense to try a simplified version without the join to check that everything else is OK.

The behaviour of DBIx::Class::ResultSet::search differs depending on the context in which it's called. If it's called in list context then it executes the database query and returns an array of MyApp::DBIC::Schema::Account objects. For example:

my @accounts = $c->model('DB::Account')->search();

In your case you're calling search in scalar context, which means that rather than returning an array it will return a DBIx::Class::ResultSet object (or a subclass thereof), and crucially it won't actually execute a db query. For that to happen you need to call the all method on your resultset. So, assuming you're using the default template toolkit view you probably want something like this:

[% FOREACH search_result IN search_results.all %]
    [% search_result.first_name %]
[% END %]

This 'lazy' behaviour of DBIx::Class is actually very useful, and in my opinion somewhat undersold in the documentation. It means you can keep a resultset in a variable and keep executing different search calls on it without actually hitting the DB, it can allow much nicer code in cases where you want to conditionally build up a complex query. See the DBIx::Class::Resultset documentation for further details.

Upvotes: 3

Related Questions