CODEWITHSUNDEEP
phpmysqlcakephp
crazy sarah
crazy sarah

Reputation: 631

cakephp - get results from two unrelated tables

I've been googling this for a while but to no avail. I'm finding the possible answers confusing and hoped someone could clear it up for me.

I've two tables (tasks and installs) which contain similar data, but not the same, and there's no relationship between the two tables, other than the fact they both belong to the same branch. So for example:

Tasks Table 

id  
branch_id  
task_name  
to_be_billed   
created

Installs Table 

id  
branch_id  
install_details  
to_be_billed   
created

I'm trying to figure out how to get a result set which would show each record from either table, arranged by date created order and only where the 'to_be_billed' column is '1'.

Can anyone give me some pointers please?

Thanks

Upvotes: 1

Views: 104

Answers (2)

Igor Lopes
Igor Lopes

Reputation: 21

I'm assuming that you want to get the results using the branch_id and these two tables (Tasks and Install) have some relationship with the BranchTable.

I'm also assuming the Tasks and Installs Table's have multiple records for a Branch.

BranchTable->find()
    ->contain([
        'Tasks' => [
            'sort' => ['Tasks.created' => 'ASC'] 
        ]
    ])
    ->contain([
        'Installs' => [
            'sort' => ['Installs.created' => 'ASC'] 
        ]
    ])
    ->matching('Tasks', function ($q){
        return $q->andWhere(['Tasks.to_be_billed' => 1]);
    })
    ->matching('Installs', function ($q){
        return $q->andWhere(['Installs.to_be_billed' => 1]);
    })
    ->where(['Branch.id' => $foo]);

If your doubt does not use these assumptions let me know.

Upvotes: 2

dfx413
dfx413

Reputation: 58

If you are trying to get the data using one DB query then you would need to use the UNION operator.

In that case you would need these two queries to have the same columns, so for example:

select 
   id,
   branch_id,
   task_name,
   NULL as install_details,
   'task' as type,
   to_be_billed,
   created
from 
   tasks_table
UNION
select 
   id,
   branch_id,
   NULL as task_name,
   install_details,
   'install' as type,
   to_be_billed,
   created
from 
   install_table

but that's a rather dirty solution.

If I knew what exactly you are trying to achieve, maybe I could suggest a better answer.

Upvotes: 0

Related Questions