CODEWITHSUNDEEP
sqlcakephpjoincontroller
Ashutosh
Ashutosh

Reputation: 4675

cakephp sql join query in controller

I'm new to CakePHP and stuck on this situation:

I'm developing a test system and I have following three tables: Category, SubCategory, Test

When I create a test, I store category_id, subcategory_id with other data in Test table Now in TestsController, I want to read the number of records & rows matched by the query

I wish to execute queries similar to these:

Query 1

select count(*) from Test where user_id='$user_id' and category_id=$category_id and subcategory_id=$subcategory_id

Query 2

select 
a.test_id,
a.test_name,
a.user_id,
a.test_type,
a.create_date,
a.create_time,
a.category_id,
a.subcategory_id,
a.randomize,
a.test_password
from Test a, Category b, SubCategory c 
where user_id='$user_id' and 
a.category_id=$category_id and
a.subcategory_id=$subcategory_id and
a.category_id=b.category_id and
a.subcategory_id=c.subcategory_id

Now, I don't want (I actually don't know) to use the $hasMany, $belongsTo etc.

I simply want to execute these queries in Controller & pass the data to the view

Note: In all simple cases, I used the CakePHP style of coding

I wish to know, how can I get an array of rows after executing Query 2 Is it possible to execute such query directly (like mysql_query)

Upvotes: 1

Views: 1038

Answers (1)

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

you could create a function in Test Model and access the query, like:

class TestModel extends Model {
    ..
    public function getQueryData($user_id, $cat_id, $subcat_id) {
       return $this->query("SELECT a.test_id, a.test_name ..... ;");
    }
}

and in Test controller, you could do:

$this->loadModel("Test");
$result = $this->Test->getQueryData($user_id, $cat_id, $subcat_id);

Upvotes: 1

Related Questions