0

I have 3 tables

  1. users
  2. bases
  3. bases_users

In usersTable I have created a belongsToMany relation like

$this->belongsToMany('Bases', [
    'foreignKey' => 'user_id',
    'targetForeignKey' => 'base_id',
    'joinTable' => 'bases_users',
]);

Now I am trying to fetch all users where user is under this login user bases.

Query like below

    SELECT users.name FROM users
INNER JOIN bases_users on bases_users.user_id = users.id
WHERE bases_users.base_id in (1,2) //login user 1 has tow bases (1,2)
GROUP BY Users.id

I'm trying to get accepted data using below query with content

$this->Users->find()
            ->contain([
                'UserAuthorities',
                'Bases' => function($q) use($userId){
                    return $q->where(['Bases.users.id' => $userId]);
        }
])

How can I match the data where BasesUsers.user_id = $userId ?

I have tried the code after @ndm comment like below

$queryUsers = $this->Users->find()
            ->contain([
                'UserAuthorities',
                'Bases'
            ])
            ->innerJoinWith('BasesUsers', function(\Cake\ORM\Query $q) {
                return $q->where(['BasesUsers.user_id' => 10]);
            })
            ->where($conditions)
            ->order($order)
        ;

Query is generating :

SELECT 
  ... 
FROM 
  users Users 
  INNER JOIN bases_users BasesUsers ON (
    BasesUsers.user_id = 10 
    AND Users.id = BasesUsers.user_id
  ) 
  INNER JOIN user_authorities UserAuthorities ON UserAuthorities.id = Users.user_authority_id 
WHERE 
  (
    Users.company_id = 1 
    AND Users.is_deleted = FALSE
  ) 
ORDER BY 
  Users.created DESC 
LIMIT 
  20 OFFSET 0

Here My expecting query is

INNER JOIN bases_users BasesUsers ON (
        BasesUsers.user_id = 10 
)

Sample data

Users table :

id, name, 

1   A
2   B
3   C
4   D

Bases table :

id   Name 
1    Base 1  
2    Base 2 
3    Base 3

BasesUsers:

id  user_id   base_id 
1   1         1
2   1         2
3   2         1
4   3         1
5   4         3 
6   3         2

Expected result for user id 1 logged in

name  bases   
A     Base1,Base2
B     Base1
C     Base1,Base2

How I will remove AND Users.id = BasesUsers.user_id from innerJoin with ?

Niloy Rony
  • 602
  • 1
  • 8
  • 23
  • Check **https://stackoverflow.com/questions/26799094/how-to-filter-by-conditions-for-associated-models** – ndm Sep 28 '22 at 13:35
  • @ndm I have update my question , How I will remove AND Users.id = BasesUsers.user_id from inner join ? – Niloy Rony Oct 01 '22 at 19:11
  • You might not be able to do that, at least not easily. But before I look into it, let me ask why would you want to remove that condition in the first place, when this is the one attribute that defines whether the involved records are actually related? – ndm Oct 01 '22 at 19:20
  • Actually trying to find all users where auth user in same base. I have basesusers table where there has user_id and base_id. – Niloy Rony Oct 01 '22 at 19:25
  • In that case you may have to resort to [**manual joins**](https://book.cakephp.org/4/en/orm/query-builder.html#adding-joins), as disabling foreign key generation is IIRC only supported for `hasOne` and `belongsTo` associations. – ndm Oct 01 '22 at 20:10
  • Note there's no reference in the question to `UserAuthorities` except to say the generated sql isn't desired - for clarity it's probably best to remove references to this model/table from the question. – AD7six Oct 02 '22 at 15:39
  • @AD7six I have added sample data with expected result. Thank you. – Niloy Rony Oct 02 '22 at 16:44
  • @ndm I have added sample data with expected result. – Niloy Rony Oct 02 '22 at 16:53
  • @AD7six Sorry My query was wrong, I have updated my possible query. example I'm trying selecting all students under a teacher classes. Query I have written now almost correct but in cakephp I don't know how I will write it using container and it may using matching. Because I have to display all bases in a user column row (Like expected result). – Niloy Rony Oct 02 '22 at 17:47
  • 1
    Thanks. There are still references to your earlier query condition question - please revise to integrate what is now known (nobody really cares if it's 'new' info, and if they do - posts have a [browsable history](https://stackoverflow.com/posts/73878424/revisions)). You almost have the answer in the question already with `I have tried the code after @ndm comment like below` - the (simplest?) solution is `return $q->where(['BasesUsers.base_id' => $query2getThoseIds])` - that may be all the hint you need to solve and self answer, it's late in the day for me :). – AD7six Oct 02 '22 at 18:00
  • @AD7six Thank you , It has solved. Is not it possible without in query ? I have solved like below return $q->where(['BasesUsers.base_id IN' => $query2getThoseIds]) – Niloy Rony Oct 02 '22 at 18:21

1 Answers1

0

I have the same problem, not exactly for compare two id's but for check if a column match a condition, i solve this using matching()

Read this of CakePHP documentation:

I use those examples and work perfect. Hope this can help you.

Rafael
  • 11