CakePHP - SQL query Join

Question

Ok, so i'm in a pickle trying to figure a way for how to do this :

I have a live search(using ajax) which allows the user to select a criteria from a dropdown list and then enter a value which will be matched to values inside the database. This is quite trivial stuff, and on direct fields of the main model, i don't have any issues.

I have a Donor Model/table which consists of attributes such as ID, Name, Surname etc, but more importantly it also has FK of other associated models such as blood_group_id, donor_type_id, which map back to the respective models (BloodGroup and DonorType).. These two are already set with the associations and I am beyond that part, as I am already retrieving Donor records with associated model data.

Here is the search method which will hopefully help you in understanding my problem better.

public function search() {
        if($this->request->is('post')){
            if(!empty($this->request->data)){
                $criteria = $this->request->data['criteria'];
                $query = $this->request->data['query'];
                $conditions = array("Donor." .$criteria. " LIKE '". $query . "%'");

The above checks if its a post request and whether data was sent. The criteria and user input are used to construct the query..

This is where my problem arises.. (When the user select search By blood type, as a criteria from the drop down)the above expects the user to enter the id of the blood_group rather than A+ or A- for instance.. So if the input is 1(id of blood group A+), the results are returned as expected. But I want the user to be able to enter A+...

Here is the rest of the method :

$this->Paginator->settings = array(
                'conditions' => $conditions,
                'limit' => 2
            );


 $donors = $this->Paginator->paginate('Donor');
            $this->set('donors', $donors);
            $this->beforeRender();
            $this->layout= 'ajax';
        } 
    }
}

I have tried this approach, setting up the conditions using the Model's name such as

   if($criteria == 'blood_group_id'){
       $conditions = array("BloodGroup.id" . " LIKE '". $query . "%'");
   }elseif($criteria == 'donor_type_id'){
       $conditions = array("DonorType.id" . " LIKE '". $query . "%'");
   }else{
       $this->Paginator->settings = array(
          'conditions' => $conditions,
          'limit' => 2
       );
   }

But this returns all the records irrespective of the input.

I also tried changing the settings for the paginator with no luck

$settings = array(
  'joins' => array(
     'table' => 'blood_groups',
     'alias' => 'BloodGroup',
     'type' => 'LEFT',
     'conditions' => array(
        "BloodGroup.id" => "Donor.blood_group_id",
        "AND" => $conditions
     )
   ),
'limit'=> 2
);

Any help on how to accomplish what I explained above, would greatly be appreciated!

@arilia it is hardcoded like so :` 'options' => array( 'id'=> 'By ID', 'name' => 'By Name', 'blood_group_id' => 'By Blood Type', 'type' => 'By Donor Type', 'age' => 'By Age', 'gender' => 'By Gender' )` — LogixMaster
– LogixMaster, Commented Feb 10, 2014 at 12:03

arilia · Accepted Answer · 2014-02-10 12:18:13Z

1

simply:

if($criteria == 'blood_group_id')
    $conditions = array("BloodGroup.name LIKE" => $query.'%');

(assuming bood_types has a 'name' column)

Also let me suggest you to use the CakeDC search plugin (https://github.com/CakeDC/search).

edited Feb 10, 2014 at 12:18

answered Feb 10, 2014 at 12:11

arilia

9,3982 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

LogixMaster Over a year ago

Thanks for pointing out the plugin :) though the query still returns all records and not filtered..

arilia Over a year ago

because your if is wrong and PHP never enters the Paginator->settings code block if $criteria == 'block_group_id'. Try and rewrite the whole if logic

LogixMaster Over a year ago

OOPS- Ofcourse!! how did I miss that! I feel so stupid and ignorant now, my bad!! Thank you for all your help !! - lesson learned- inspect all code especially the most trivial. sometimes I overlook these SILLY mistakes

LogixMaster Over a year ago

Arilia, can you please point me to a good tutorial which can get me started on cakeDC search. so far, I can't quite seem to understand how it works, and without insulting the developers who created cakeDC search, the documentation seems to lack the correct grammar to a point which I do not understand it, apart from being very brief. I have watched this guy's tutorial, which is better than nothing, but still not quite there yet.

arilia Over a year ago

maybe the best way is that you try some code yourself and when you are stuck you post another question here on SO providing the code you'll have tried so far

|

Collectives™ on Stack Overflow

CakePHP - SQL query Join

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related