When Join - limit problem

[omerorhan]

Hello,

for example ;

i want to select 5 products per page with extra joins depending on client's choise.

if just from 1 table its Ok.

product 1
product 2
product 3
product 4
product 5

However if i want to take extra information from another table with join.
I got this. 5 records ,but 3 Products.

Product 1 - category 1
Product 1 - category 2
Product 1 - category 3
Product 2 - category 1
Product 3 - category 1

I should get

Product 1 - category 1
Product 1 - category 2
Product 1 - category 3
Product 2 - category 1
Product 3 - category 1
Product 4 - category 1
Product 4 - category 2
Product 5 - category 1
Product 5 - category 3

How could i get this in just 1 query?

I want to learn the logic.

In real world

[0] => Array
        (
            [id] => 44
            [code] => 375641
            [pic] => imag0298
            [category] => Category 1
        )

    [1] => Array
        (
            [id] => 44
            [code] => 375641
            [pic] => imag0298
            [category] => Category 2
        )
    [2] => Array
        (
            [id] => 44
            [code] => 375641
            [pic] => imag0298
            [category] => Category 3
        )
[3] => Array
        (
            [id] => 45
            [code] => 375642
            [pic] => imag0299
            [category] => Category 1
        )
[4] => Array
        (
            [id] => 46
            [code] => 375643
            [pic] => imag0300
            [category] => Category 1
        ) 

What i need is

[0] => Array
        (
            [id] => 44
            [code] => 375641
            [pic] => imag0298
            [categories] => Array
                                    [0] = > Category 1
                                    [1] = > Category 2
                                    [2] = > Category 3
        )
[1] => Array
        (
            [id] => 45
            ............
            [categories] => Array
                                    .....
        )
[2] => Array
        (
            [id] => 46
            ..........
            [categories] => Array
                                    .....
        )
[3] => Array
        (
            [id] => 47
            ..........
            [categories] => Array
                                    .....
        )
[4] => Array
        (
            [id] => 48
            ..........
            [categories] => Array
                                    .....
        ) 

Also , is there a way that i can get result as multidimensial array from MYSQL?

I hope i could explain the problem.

Thank you

[r937]

to pull 5 products, you could approach it in two ways

first, run a simple query, along with the desired LIMIT values to implement paging, and then, having retrieved the 5 products, issue another query to pull in the related data, using an IN list of product ids

alternatively, you could put the LIMIT into a subquery, and use that to join to the other tables (LIMIT in a subquery didn't always used to work, but i believe in version 5 there's no problem)

as for your other question, mysql doesn't return any kind of array, never mind a multidimensional array -- instead, mysql ~always~ returns a tabular result set, consisting of rows and columns

[omerorhan]

Thank you for your reply.

but i couldn't understand ; if i have to use limit for subquery or main query.


select(products.name, products.slug ...) -> from -> products ->
( select(cat.name, cat.id ) -> from(categories as cat)-> where cat.productId IN products.id ) as catColumn
->limit(5);

Is that logic true?

If it's true , query like above gave me error " Subquery returns more than 1 row ".
As what i was exactly want to have, but how to get rid off error?

I used both where and where in ?
I read MySQL :: MySQL 5.0 Reference Manual :: 12.2.9.5 Row Subqueries , couldn't fix it.

And also, i use code igniter framework.

i search a lot and :

$sub= $this->db->select('cats.category_id , cats.category_name')->from('categories cats')->where_in('cats.product_id', 'product.id')->_compile_select(); //attention to 'product_id' here 
 $this->db->_reset_select();

$category_condition = $module_info[0]['category_show'] != 0 ?  ',(  '.$sub.'  ) as catColumn' :"";
$limit = 5;
$offset = 0;

//and then my main query

$map_condition = " ...select map columns... or not";

$this->db->select('pDetail.name as productName , product.id,product.code, product.picture '.$category_condition .$map_condition ,false);
$this->db->from('products product');
$this->db->join('product_details pDetail','pDetail.product_id = product.id && pDetail.lang_id = 1', 'left');
$this->db->limit($limit , $offset);
// There are other conditions to join another tables 

for each products , If there are more than one category in categories table , it gave an "Subquery returns more than 1 row" error.

If i fixed it, another thing will be a problem is : Above i couldn't use 'product.id' in where clause from main to subquery. For testing i am giving manual numbers.

Thank you

[r937]

select(products.name, products.slug ...) -> from -> products ->
( select(cat.name, cat.id ) -> from(categories as cat)-> where cat.productId IN products.id ) as catColumn
->limit(5);

Is that logic true?

i have no idea, because i don't recognize that language (and it isn't SQL)

why don't you show me your actual SQL query that returns both products and categories

[omerorhan]

It was just an example. Sorry for not to explain

This is my real query.

Subquery returns more than 1 row

SELECT ilanlar.id, ilanlar.emlak_kod, ilanlar.tipID, ilan_resimleri.file_slug as resim, ilan_resimleri.ext, ilan_detaylari.baslik, ilan_detaylari.alias, ilanlar.map_durum, emlak_maps.lat, emlak_maps.lng, emlak_maps.map_address, emlak_maps.icon, emlak_maps.map_zoom, ( SELECT `atanmis_cat`.`kategori_id` FROM (`emlak_ilan_secili_kategoriler` atanmis_cat) WHERE `atanmis_cat`.`ilan_id` IN (44) ) as kats, bolgeler.isim as bolge, ilanlar.bolge_id, ilanlar.m2, para_birimleri.isim as para_birimi, ilanlar.fiyat, ilanlar.fiyatGizle, islem_tipleri_detay.isim as tipi, ilanlar.islemID, emlak_turleri_detay.isim as turu, ilanlar.turID FROM (emlak_ilanlar ilanlar) LEFT JOIN emlak_ilan_detaylari ilan_detaylari ON ilan_detaylari.ilanID = ilanlar.id && ilan_detaylari.lang_id =1 LEFT JOIN emlak_ilan_resimleri ilan_resimleri ON ilan_resimleri.ilanId = ilanlar.id && ilan_resimleri.temsili = 1 LEFT JOIN emlak_maps ON ilanlar.id = emlak_maps.ilan_id LEFT JOIN emlak_para_birimleri para_birimleri ON para_birimleri.id = ilanlar.para_birimi LEFT JOIN emlak_turleri_detay ON emlak_turleri_detay.tur_id = ilanlar.turID && emlak_turleri_detay.lang_id=1 LEFT JOIN emlak_islem_tipleri_detay islem_tipleri_detay ON islem_tipleri_detay.tip_id = ilanlar.islemID && islem_tipleri_detay.lang_id=1 LEFT JOIN emlak_bolgeler bolgeler ON bolgeler.id = ilanlar.bolge_id && bolgeler.durum = 1 WHERE ilanlar.islemID IN ('1') AND `ilan_detaylari`.`lang_id` = '1' AND `ilanlar`.`ilanDurum` = 1 ORDER BY fiyat asc LIMIT 5

[r937]

here's a hint for future threads: please format your SQL so that a human being, not just a database engine, can read and understand it

SELECT ilanlar.id
     , ilanlar.emlak_kod
     , ilanlar.tipID
     , ilan_resimleri.file_slug as resim
     , ilan_resimleri.ext
     , ilan_detaylari.baslik
     , ilan_detaylari.alias
     , ilanlar.map_durum
     , emlak_maps.lat
     , emlak_maps.lng
     , emlak_maps.map_address
     , emlak_maps.icon
     , emlak_maps.map_zoom
     , ( SELECT `atanmis_cat`.`kategori_id` 
           FROM (`emlak_ilan_secili_kategoriler` atanmis_cat) 
          WHERE `atanmis_cat`.`ilan_id` IN (44) ) as kats
     , bolgeler.isim as bolge
     , ilanlar.bolge_id
     , ilanlar.m2
     , para_birimleri.isim as para_birimi
     , ilanlar.fiyat
     , ilanlar.fiyatGizle
     , islem_tipleri_detay.isim as tipi
     , ilanlar.islemID
     , emlak_turleri_detay.isim as turu
     , ilanlar.turID 
  FROM (emlak_ilanlar ilanlar) 
LEFT 
  JOIN emlak_ilan_detaylari ilan_detaylari 
    ON ilan_detaylari.ilanID = ilanlar.id 
    && ilan_detaylari.lang_id = 1 
LEFT 
  JOIN emlak_ilan_resimleri ilan_resimleri 
    ON ilan_resimleri.ilanId = ilanlar.id 
    && ilan_resimleri.temsili = 1 
LEFT 
  JOIN emlak_maps 
    ON ilanlar.id = emlak_maps.ilan_id 
LEFT 
  JOIN emlak_para_birimleri para_birimleri 
    ON para_birimleri.id = ilanlar.para_birimi 
LEFT 
  JOIN emlak_turleri_detay 
    ON emlak_turleri_detay.tur_id = ilanlar.turID 
    && emlak_turleri_detay.lang_id = 1 
LEFT 
  JOIN emlak_islem_tipleri_detay islem_tipleri_detay 
    ON islem_tipleri_detay.tip_id = ilanlar.islemID 
    && islem_tipleri_detay.lang_id = 1 
LEFT 
  JOIN emlak_bolgeler bolgeler 
    ON bolgeler.id = ilanlar.bolge_id 
    && bolgeler.durum = 1 
 WHERE ilanlar.islemID IN ('1') 
   AND `ilan_detaylari`.`lang_id` = '1' 
   AND `ilanlar`.`ilanDurum` = 1 
ORDER 
    BY fiyat asc LIMIT 5

so the problem is that the subquery returns more than one row?

try changing this --

     , ( SELECT `atanmis_cat`.`kategori_id` 
           FROM (`emlak_ilan_secili_kategoriler` atanmis_cat) 
          WHERE `atanmis_cat`.`ilan_id` IN (44) ) as kats

to this --

     , ( SELECT GROUP_CONCAT(kategori_id) 
           FROM emlak_ilan_secili_kategoriler 
          WHERE ilan_id = 44 ) as kats

[omerorhan]

That worked.
Thank you so much.

[omerorhan]

One more question;

I don't get only kategori_id with subquery, i also get category_name and slug,

I will use them to make links.

What should i do now?

I tried like this

( SELECT GROUP_CONCAT(CONCAT_WS("+",atanmis_cat.kategori_id , atanmis_cat_detay.isim , atanmis_cat_detay.slug) order by atanmis_cat_detay.isim)  
                  from emlak_ilan_secili_kategoriler atanmis_cat 
                  left join emlak_kategorileri_detay atanmis_cat_detay on atanmis_cat_detay.root_id = atanmis_cat.kategori_id && atanmis_cat_detay.lang_id = 1
		  where atanmis_cat.ilan_id = ilanlar.id ) as CATEGORI

I got this:

[CATEGORI] => 2+Category name 1+slug 1, 6+category name 2 +slug 2, 4+category name 3+slug 3

Is that the right way? or should i do something different?

Because after that , i have to use php preg_split function to get id , category_name and slug again.

What i am doing is Right? or there is a better way you should advise?

For example ; can i prepare links with mysql already? Is there a way mysql doing this.

Thanks again.

[r937]

I don't get only kategori_id with subquery, i also get category_name and slug

then what you gave me earlier wasn't your "real" query

Is that the right way?

that's a good solution

concatenate the columns first with CONCAT_WS, then concatenate the multiple row columns together with GROUP_CONCAT

this ensures that the subquery returns only 1 row for each ilanlar.id

yes, you have to "post-process" the results in php in order to construct your category links

[omerorhan]

thank you so much my friend thank you