MySQL search doesn't work properly in Catalyst framework

Question

I am working on a MySQL database that is accessible via browser using the Catalyst framework. The user can search the database from a search box using a specific ID (car plate) and/or a specific car color.

The problem is that when I search for some IDs that exist in the database they don't show up in the results whereas others do. All of the IDs that I test can be accessed properly if I manually change the URL in the browser, so I guess the problem lies in my search function.

My function in the controller is this

sub search : Local : Args(0) {

  my ($self, $c) = @_;

  my $schema           = $c->model('DB');
  my $car_plate        = $c->request->params->{car_plate} || 'N/A';
  my $car_model        = $c->request->params->{car_model} || 'N/A';
  my $result_set       = "";
  my $result_set_count = 0;

  if (($car_plate ne 'N/A') && ($car_model ne 'Select model')) { # car plate AND car model entered
    $result_set = [
      $schema->resultset('Car')->search({
          'plates' => { 'like', '%' . $car_plate . '%' },
          'colors' => $car_model
        },
      )->all
    ];
  }

  elsif (($car_plate eq 'N/A') && ($car_model ne 'Select model')) { # car plate blank, car model entered
    $result_set = [
      $schema->resultset('Car')->search(
        { 'colors' => $car_model },
      )->all
    ];
  }

  elsif (($car_plate ne 'N/A') && ($car_model eq 'Select model')) { # car plate entered, car model blank
    $result_set = [
      $schema->resultset('Car')->search(
        { 'plates' => { 'like', '%' . $car_plate . '%' } },
      )->all
    ];
  }

  if (($car_plate ne 'N/A') && ($car_model ne 'Select model')) {
    $result_set_count = $c->model('DB::Car')->search({
        'plates' => { 'like', '%' . $car_plate . '%' },
        'colors' => $car_model
      }
    )->all;
  }
  elsif (($car_plate eq 'N/A') && ($car_model ne 'Select model')) {
    $result_set_count = $c->model('DB::Car')->search({ 'colors' => $car_model })->all;
  }
  elsif (($car_plate ne 'N/A') && ($car_model eq 'Select model')) {
    $result_set_count = $c->model('DB::Car')->search({ 'plates' => { 'like', '%' . $car_plate . '%' } })->all;
  }

  my $error_message = "";

  if ($result_set_count == 0) {

    $error_message = "<p>Not found</p>";
    $c->stash(
      errorcount => $error_message,
      Cars       => $result_set,
      template   => 'cars/search_no_results.tt'
    );
  }
  else {

    $c->stash(
      errorcount => $error_message,
      Cars       => $result_set,
      template   => 'cars/search_cars.tt'
    );
  }

  $c->response->header('Cache-Control' => 'no-cache');
}

Julien · Answer 1 · 2014-03-07T02:33:05.887

3

$result_set_count would be an array (you don't actually use the count() function)

Replace lines like

$result_set_count = $c->model('DB::Car')->search({ 'plates' => { 'like', '%' . $car_plate . '%' } })->all;

by

$result_set_count = $c->model('DB::Car')->count('plates' => { 'like', '%' . $car_plate . '%' });

Then, optimize you code by doing each DB query once:

my $rs = $c->model('DB::Car')->search_rs({ ... }, { });
my $count = $rs->count;
my $set = [ $rs->all ];

edited Mar 07 '14 at 02:33

answered Feb 28 '14 at 00:45

Julien

5,729
4
37
60

Thank you very much for your help. I followed your suggestion but unfortunately it doesn't work properly. When I hit a specific ID that exists in the database it returns not found. I also see this output: DBIx::Class::ResultSet::count(): search( %condition ) is deprecated, use search( \%condition ) instead. What am I doing wrong? – john john Mar 05 '14 at 23:07
my $rs = $c->model('DB::Car')->search({ 'plates' => { 'like', '%' . $car_plate . '%' } }, { }) – Julien Mar 07 '14 at 02:33
Thank you again for your help. Still doesn't work. The strange thing now is that if for example I want to see the results for the ID:14367 it renders an empty result table instead of a not found message. But when I search for 1436 it renders the record with ID 14367 in the result table. – john john Mar 07 '14 at 13:16

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

Have you tried getting the SQL output produced by DBIx::Class and then running that directly in MySQL?

See: Can I pretty-print the DBIC_TRACE output in DBIx::Class?

edited May 23 '17 at 10:26

Community

1
1

answered Mar 07 '14 at 19:37

oyse

1,023
9
18

MySQL search doesn't work properly in Catalyst framework

2 Answers2