We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Phalcon search query

Hello,

I have to make query that search over 3 tables in my DB. Il make a example:

I get request via URL from my front-end search form something like this:

/cars/search/{condition}/{type}/{location}

DB name:

  • cars

DB tables:

  • cars
  • cars_types
  • cars_locations

Now, if i get request something like this: /cars/search/new/BMW/NewYork Result is: Give me all cars wich are in new condition, type is BMW, from location New York.

But, if i get request something like this: /cars/search/all/all/NewYork

Results must be: Give me cars with any conditions, any types from specific location = NewYork.

How to make this "all" parameter default (so when i recive all in parameter, that must meen give me all from that table), and what is the best way to retrive this data..



85.5k
if ($param !=='all'){
//add where
}

show us the current code you got



23.6k

In my routes.php

$router->addGet("/cars/search/{condition}/{type}/{location}", array(
    'module' => 'cars',
    'controller' => 'cars',
    'action' => 'search',
    ))

in my carsController.php

public function searchAction()
    {
        // Get "condition" parameter
        $condition = $this->dispatcher->getParam("condition");

        // Get "type" parameter
        $type = $this->dispatcher->getParam("type");

        // Get "location" parameter
        $location = $this->dispatcher->getParam("location");

//part where query come ... 


85.5k

i still dont see where you assign where params to the sql, or you solved it based on my previous post ?



43.9k

Why not use simple if/else statement :

if($condition == 'all'){ ....}

Just use if else and generate proper where string and bind array.



23.6k

i made and big sql query where I adding parameters like this... but im holding all this in controller .. I think i should move it to model or processor.

// Parameters array
$options = array();

// Adding condition parameter if not all
   if ($condition =='all') {
   } else {
   $sql .= " AND c.condition = :condition:";
   $options['condition'] = $condition;
   }

// Adding type parameter
   if ($type !=='all'){
   $sql .= " AND ct.type = :type:";
   $options['type'] = $type;
   }

// Adding location parameter
   if ($location !=='all'){
   $sql .= " AND cl.location = :location:";
   $options['location'] = $location;
   }

Is it possible to make and ORM way to do this ? since this SQL query is really big ..

Ofc its possible, but it will be same as big :D You can put anywhere you want, im putting it in helper(static function where i pass params and returning array(where and bind)



23.6k

Ofc its possible, but it will be same as big :D You can put anywhere you want, im putting it in helper(static function where i pass params and returning array(where and bind)

Il put it in processor, but I have problems with executing query atm ... When i var_dump($sql) .. everything works fine, wherever I put "/all/" in my URL ... it removes that parameter from query.. Should i use this and pass my $sql and $options or ? https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql

Why you doing raw sql btw ?



23.6k

Why you doing raw sql btw ?

I just started with phalcon friend .. do you know bether way to do this whole thing? I cant find any complexed example in ORM Documentation like this one ...

edited Nov '15

You can just models find/findFirst static methods or modelsManager ?



23.6k

You can just models find/findFirst static methods or modelsManager ?

Is there any example of that.. I need example of dynamir query-s like mine ... On documentation and ORM, I cant find any complexed query like mine ...



145.0k
Accepted
answer
edited Nov '15

What you meen by complex query ? I just see normal query where you selecting records where some columns = some values, you can just use:

YourModel::find(array( 'conditions'=>'where query', 'bind'=>bind params ));

If you are searching on foreign keys then its enough.

Or if you want to search in relaed records:

$modelsManager->createBuilder->
    ->from(array('Cars'=>'Cars Model with Namespace'))
    ->where('where query',bind array)
    ->leftJoin('namespace + model','condition','CarsTypes') # or any other join you want
    ->leftJoin('namespace + model','condition','CarsLocations') # or any other join you want
    ->getQuery()
    ->execute();

$modelsManager is modelsManager which you can get from di.

For me it looking much better.



23.6k

What you meen by complex query ? I just see normal query where you selecting records where some columns = some values, you can just use:

YourModel::find(array( 'conditions'=>'where query', 'bind'=>bind params ));

If you are searching on foreign keys then its enough.

Or if you want to search in relaed records:

$modelsManager->createBuilder->
  ->from(array('Cars'=>'Cars Model with Namespace'))
  ->where('where query',bind array)
  ->leftJoin('namespace + model','condition','CarsTypes') # or any other join you want
  ->leftJoin('namespace + model','condition','CarsLocations') # or any other join you want
  ->getQuery()
  ->execute();

$modelsManager is modelsManager which you can get from di.

For me it looking much better.

Yeah friend, but im reciving dynamic parameters... look on my main post.. What will happen when I recive /all/ in parameters .. he will search in database for it, but what i need is the when he recives parameter /all/ = give me all from that table.

It's not about query is about application logic, just when you have all you don't add this condition.



23.6k

It's not about query is about application logic, just when you have all you don't add this condition.

Yeah but, how to write that in ORM.. Is there any example of that? Every example that I find in documentation is too simple ..

edited Nov '15

But ORM is diffrent thing than application logic. You have to write conditions etc. Like above, it has nothing to do with ORM, SQL, QUERYS etc etc. Ofc you can do the same, but its not about Phalcon, Not about ORM, only about SQL. If you can do the same in php better to do in php(in this case at least)



23.6k

Thank you, I change some application logic and did it with ORM :D