CakePHP: What's the way to use an SQL query statement in an MVC-like structure

Question

I wonder how do we use this in an MVC framework, CakePHP in particular.

$query = oci_parse($c, "SELECT * FROM JOB ORDER BY job_title");
                        oci_execute($query);

                        while($row = oci_fetch_assoc($query)){
                            $showRowJ .= "<tr>\n";
                            $showRowJ .= "<td><a href = 'job_delete.php?jobid=".$row['JOB_ID']."'> Delete </a></td>\n";
                            $showRowJ .= "<td><a href = 'job_update.php?jobid=".$row['JOB_ID']."'> ".$row['JOB_ID']." </a></td>\n";
                            $showRowJ .= "<td>".$row['JOB_TITLE']."</td>\n";
                            $showRowJ .= "<td>".$row['REQUEST_DATE']."</td>\n";
                            $showRowJ .= "<td>".$row['START_DATE']."</td>\n";
                            $showRowJ .= "<td>".$row['NUMBER_OF_DAYS']."</td>\n";
                            $showRowJ .= "<td>".$row['STATUS']."</td>\n";
                            $showRowJ .= "<td>".$row['CLIENT_ID']."</td>\n";
                            $showRowJ .= "</tr>\n";
                            }

and if for MySQL: (example)

$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

I'm confused some say it should be in a model and some are using find('all')

UPDATE:

Hey sorry I meant when we want to query joint tables. The above is just an example when we need to include WHERE 2-3 times for joining tables.

Let's say I want to query:

SELECT users.name, foods.name FROM users, foods WHERE users.id='1' AND users.id=foods.id

Something like above.. How should we do it in CakePHP ?

Thanks.

score 3 · Accepted Answer · answered Jan 31 '11 at 08:53

3

Since Cake includes a complete database abstraction layer, you wouldn't write any sort of SQL at all. You'd set up your models correctly and then to something along these lines:

Controller

$jobs = $this->Job->find('all', array('order' => array('Job.title' => 'asc')));
$this->set(compact('jobs'));

View

<table>
<?php foreach ($jobs as $job) : ?>
    <tr><?php echo htmlentities($job['Job']['title']); ?></tr>
    ...
<?php endforeach; ?>
</table>

Maybe you should follow the tutorial. :)

answered Jan 31 '11 at 08:53

deceze

510,633
85
743
889

Hi does that mean even for joint tables? – George Leow Jan 31 '11 at 09:03
3

@John I really suggest you follow the tutorial and read the manual. With properly set up models, Cake automatically fetches associated data (sometimes using JOINs, sometimes not). There are also ways to explicitly do JOINs when necessary. – deceze Jan 31 '11 at 09:12
No deceze, it's my fault I didn't phrase my question properly, though some may understand but it's http://stackoverflow.com/questions/806650/in-cakephp-how-to-retrieve-joined-result-from-multiple-tables I was trying to ask. But I couldn't understand still... Thanks for answering. – George Leow Jan 31 '11 at 09:15
1

@John I really don't know what your question is then. It seems you're unsure of how to retrieve data using Cake's DAL. This has nothing to do with MVC, it's just the DAL you don't know. I suggest again you read the manual to get a better grasp of it. You're **not** writing SQL queries in Cake, unless you absolutely can't avoid it. How to get the Cake DAL to retrieve the data you want is an entire topic in itself which you have to learn. In most basic cases something like the above will work just fine. If you have more special cases, you will need to ask more specific questions. – deceze Jan 31 '11 at 23:57
Actually I solved the problem thanks to the explanation. Had a hard time ticking the right answer and finally decided to choose yours. – George Leow Feb 01 '11 at 01:54

Dunhamzzz · Answer 2 · 2011-01-31T16:55:36.810

2

CakePHP will do all the SQL for you, all you need to do is load the model, you will automatically have this as $this if using the MVC setup and you're in the right controller.

// Get jobs and assign them to a var.
$jobs = $this->findAllByUserId($id, array('order' => 'job_title');
// Send the var to the view.
$this->set(compact($jobs));

Where findAllByUserId($id) means "Find all rows where user_id = $id".

Then simply loop through $jobs in your view. Cake automatically gets all associated models as long as you have the relationships defined properly with $hasOne, $belongsTo etc in your models. See database relationships in CakePHP for more info.

CakePHP is strict to its MVC model, you will want to read the blog tutorial, which outlines the basics and has some decent examples.

Update: Updated the code to use findAllbyX which can gather all rows based on a certain field. If you want to do anything else with CakePHP you will need to fully read the CakePHP tutorial, as it will cover all the basic stuff like this.

edited Jan 31 '11 at 16:55

answered Jan 31 '11 at 08:55

Dunhamzzz

14,682
4
50
74

I have updated my answer, CakePHP will automatically do the joins to all related tabled if you have all the database relationships defined in the models. – Dunhamzzz Jan 31 '11 at 09:14
No, when we view the page, I only want the posts which have been posted by the user himself/herself to be on the view page, something like links. What the tutorial currently does is showing all posts by anyone/everyone. – George Leow Jan 31 '11 at 13:34
1

Updated my answer, what you're actually asking is very basic data retrieval, you should do some more research before developing with CakePHP. – Dunhamzzz Jan 31 '11 at 16:57
Both the answers here are equally right and I have to pick one. Thanks for the help. :-) – George Leow Feb 01 '11 at 01:59

score 0 · Answer 3 · answered Feb 22 '13 at 02:29

First, you have to declare at least a "User" class as you model like:

class User extends AppModel {
     public $actsAs = array('containable');
     public $hasMany = array('Food');

}

$actsAs is the way you can attach a behavior to your model, in this case say that this model can "contain" or join with other models.

$hasMany tell that your model can join to what models.

Then in your controller you can tell your main model what other models it should contain in a query:

public function doSomething() {
     $this->User->contain(array('Food'));
     $user = $this->User->findById(1);
     $this->set(compact($user));
}

See more detail on how can you link your models together here: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

By the way in your query

SELECT users.name, foods.name FROM users, foods WHERE users.id='1' AND users.id=foods.id

It should be

 users.id = foods.user_id

CakePHP: What's the way to use an SQL query statement in an MVC-like structure

3 Answers3