Cakephp complex query with privacy

Question

I want to create a complex query with pagination. Here is my query scenario please help me to create it

Friends table 
id | user_id | friend_id
1    2         3
2    4         2
3    5         1

friend id belong to user table id. and user_id is also belong to user table id. Friends table so many relation save so many users

user table
id name 
1  jaskaran
2  kaka
3  rajal
4  name
5  john

projects 
id user_id title privacy
1  2       abc1  1
2  2       abc2  2
3  2       abc3  3
4  2       abc3  4 

privacy 1 public
privacy 2 private
privacy 3 only selected friend
privacy 4 all friend only

proect_selected_friends

project_id | user_id
3          | 3

Now when user search on project where title="%abc%" show all record now i want to apply conditions privacy there

1 project display all the user
2 project display no one
3,4,1 project display, which has user_id 3 
4,1 project display to 3 or 4

please help me

You need to understand what sql gives you the results you want first - which isn't cakephp specific. — AD7six
What do you want your query to return? Can you provide some sample data and expected results? — Gordon Linoff
Actually I AM new in mysql and db i have no idea how to make a long query :( — user1865393

Laguna Web Design Laguna Web Design · Accepted Answer · 2014-03-20T18:10:09

You can start read about retrieving Your data: http://book.cakephp.org/2.0/en/models/retrieving-your-data.html. Examples:

$posts = $this->Project->find('all', array(
    'conditions' => array(
        'Project.user_id' => $user_id
    )
));

It's means: find all posts (all projects) from model Project (db table projects) that belongs to one User with id $user_id. Where user_id is a foreign key to db table users (model User).

$post = $this->Project->find('first', array(
    'conditions' => array(
        'Project.id' => $project_id
    )
));

It's means: find first post (first project) from model Project (db table: projects) with id = $project_id. You have to write this to your function in /app/Controller/ProjectsController.php

With pagination it will be like:

        $this->paginate = array('all', 
            'conditions' => array(
                'Project.id' => $project_id
            ),
            'limit' => 6,
        );
       $posts = $this->paginate($this->Project);

Pleas write it, if You need more complex query example.

Cakephp complex query with privacy

1 Answers