CakePHP join with comma separated ids

Question

Here I want to join two table with comma separated ids

For example my data is like:

[Restaurant] => Array
(
    [RST_ID] => 171
    [RST_NAME] => oneone
    [RST_IMAGE] => 
    [RST_CAT_ID] => 2,4,6
    [RST_CT_ID] => 27
    [RST_IS_TOP] => 3
    [RST_QR_CODE] => 
    [RST_CREATED_DATE] => 1394536725
    [RST_MODIFIED_DATE] => 1394536725
    [RST_STATUS] => 1
)

[Category] => Array
(
    [CAT_ID] => 2
    [CAT_NAME] => Vegetarian
    [CAT_CREATED_DATE] => 1375175962
    [CAT_MODIFIED_DATE] => 1375175962
    [CAT_STATUS] => 1
)

My Model Code:

 var $belongsTo = array(
        'Category' => array(
           'className' => 'Category',
           'foreignKey' => 'RST_CAT_ID',
           'conditions' => array('Category.CAT_ID IN ( Restaurant.RST_CAT_ID)')
        )
);

Real Query:

SELECT 
  `Restaurant`.`RST_ID`, `Restaurant`.`RST_NAME`, `Restaurant`.`RST_IMAGE`,
  `Restaurant`.`RST_CAT_ID`, `Restaurant`.`RST_CT_ID`, `Restaurant`.`RST_IS_TOP`,
  `Restaurant`.`RST_QR_CODE`, `Restaurant`.`RST_CREATED_DATE`,
  `Restaurant`.`RST_MODIFIED_DATE`, `Restaurant`.`RST_STATUS`, 
  `Category`.`CAT_ID`, `Category`.`CAT_NAME`, `Category`.`CAT_CREATED_DATE`,
  `Category`.`CAT_MODIFIED_DATE`, `Category`.`CAT_STATUS`, `City`.`CT_ID`,
  `City`.`CT_NAME`, `City`.`CT_CREATED_DATE`, `City`.`CT_MODIFIED_DATE`,
  `City`.`CT_STATUS` 
FROM `dailybit_dailybites`.`restaurant` AS `Restaurant`
LEFT JOIN `dailybit_dailybites`.`category` AS `Category` 
       ON (`Restaurant`.`RST_CAT_ID` = `Category`.`CAT_ID` 
           AND `Category`.`CAT_ID` IN ( `Restaurant`.`RST_CAT_ID`))
LEFT JOIN `dailybit_dailybites`.`city` AS `City` 
       ON (`Restaurant`.`RST_CT_ID` = `City`.`CT_ID`)  
WHERE 1 = 1

So what’s the solution here?

It's giving me just one category data that for first id only.

score 1 · Accepted Answer · edited May 23 '17 at 11:53

First have a look at this question: MySQL search in comma list

As you can see the belongsTo query is just generating a join on the single id, CakePHP by default doesn't respect this special case. You will have to alter your query and pass all the ids manually, but your DB design is bad and it doesn't follow the CakePHP conventions at all.

How do you prevent duplicates (which would waste space)
How do you remove a given value (Requires custom function, leading to possibility of errors?
How do you respond to performance issues as the size of my tables increase?

Instead of changing the query you should change this awkward DB design. You want to use HABTM here and a join table: Restaurant hasAndBelongsToMany Categoryy.

restaurants <-> restaurants_categories <-> categories

If you insist on using this bad DB design you'll have to use bindModel() and set the conditions manually:

'conditions' => array('FIND_IN_SET (Category.CAT_ID, ' . $listOfIds. ')')

I haven't tested this, try it yourself, see FIND_IN_SET() vs IN()

You'll have to have another method that gets you all the ids you want here before. Like I said, this is ineffectice and bad design.

thanks for your suggestion regarding db design :), but can not use your second solution b'coz if we will add condition in this way will add that in query like this:**LEFT JOIN `dailybit_dailybites`.`category` AS `Category` ON (`Restaurant`.`RST_CAT_ID` = `Category`.`CAT_ID` AND `Category`.`CAT_ID` IN ( `Restaurant`.`RST_CAT_ID`))** — Er.KT, Mar 13 '14 at 10:45
Your query doesn't show FIND_IN_SET. Set the FK in the association to false and add the join condition to the conditions array instead. Why don't you just fix your DB design instead of messing with the symptoms of a bad DB design? — floriank, Mar 13 '14 at 10:50
you are right burzum but on this stage can not redesign database, its already live project and so older one. — Er.KT, Mar 13 '14 at 10:58

score 0 · Answer 2 · edited Mar 11 '19 at 05:28

0

You have to set your foreign Key false and find_in_set condition

 var $belongsTo = array(
        'Category' => array(
           'className' => 'Category',
           'foreignKey' => false,
           'conditions' => array('FIND_IN_SET(Category.CAT_ID,Restaurant.RST_CAT_ID)')
        )
);

// you can pass an array at the place of 'Restaurant.RST_CAT_ID'

edited Mar 11 '19 at 05:28

pavan sharma

3
2

answered Aug 22 '15 at 11:18

Chirag Shah

1,463
2
18
40

'conditions' => array('FIND_IN_SET(Category.CAT_ID,Restaurant.RST_CAT_ID)'), causing error for me, as find_in_set receivs 2 parameter and we are passing more than 2 ('FIND_IN_SET(Category.CAT_ID,2,4)') so it's throwing error. So can you please suggest me something please @chirag – bikash.bilz Dec 11 '17 at 14:25

CakePHP join with comma separated ids

2 Answers2