SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    When Join - limit problem

    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

    PHP Code:
    [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

    PHP Code:
    [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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :

    PHP Code:

    $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'] != ?  ',(  '.$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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by omerorhan View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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 --
    Code:
         , ( SELECT `atanmis_cat`.`kategori_id` 
               FROM (`emlak_ilan_secili_kategoriler` atanmis_cat) 
              WHERE `atanmis_cat`.`ilan_id` IN (44) ) as kats
    to this --
    Code:
         , ( SELECT GROUP_CONCAT(kategori_id) 
               FROM emlak_ilan_secili_kategoriler 
              WHERE ilan_id = 44 ) as kats
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That worked.
    Thank you so much.

  8. #8
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    ( 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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by omerorhan View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you so much my friend thank you


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •