SQL join into join create a good array

Question

Hi I want to make a good query to have a good array. Now for example I have this query:

SELECT DISTINCT * FROM products 
        LEFT OUTER JOIN  product_aliases 
        ON product_aliases.product_id = products.id 
        AND product_aliases.alias = '$alias'
        LEFT OUTER JOIN  (product_images
            LEFT OUTER JOIN  product_image_votes 
                    ON product_image_votes.product_image_id = product_images.id)
        ON product_images.product_id = products.id 
WHERE products.id = $id

The result is two array like this:

array(
(int) 0 => array(
    'products' => array(
        'id' => '1',
        'user_id' => '1',

    ),
    'product_aliases' => array(
        'id' => '1',
        'product_id' => '1',
        'language' => 'it',
    ),
    'product_images' => array(
        'id' => '1',
        'product_id' => '1',
    ),
    'product_image_votes' => array(
        'id' => '2',
        'product_image_id' => '1',
        'vote' => '1',
    )
),
(int) 1 => array(
    'products' => array(
        'id' => '1',
        'user_id' => '1',

    ),
    'product_aliases' => array(
        'id' => '1',
        'product_id' => '1',
        'language' => 'it',
    ),
    'product_images' => array(
        'id' => '1',
        'product_id' => '1',
    ),
    'product_image_votes' => array(
        'id' => '2',
        'product_image_id' => '1',
        'vote' => '1',
    )
)

The problem is that: I want only a unique array that contain product and for example product_images that contain in an array product_images_votes. The first problem is:

have a unique array and not two arrays
create array inside array in base of my left join annidate

Example of array:

array(
(int) 0 => array(
    'products' => array(
        'id' => '1',
        'user_id' => '1',

    ),
    'product_aliases' => array(
        'id' => '1',
        'product_id' => '1',
        'language' => 'it',
    ),
    'product_images' => array(
        'id' => '1',
        'product_id' => '1',
        array('product_image_votes' => array(
            'id' => '2',
            'product_image_id' => '1',
            'vote' => '1',
        ))
    )

Is possible to do ? I'm working with php

Michael Berkowski · Accepted Answer · 2013-01-27 20:29:12Z

2

Your query is good as it is, but you need to construct the nesting in PHP. You cannot really produce nested structures in SQL alone so you must work with the flattened structure as you have it.

This can be done with some creative looping. Create an output array which is indexed by products['id']. On each iteration, create a new entry if it does not already exist. If it does exist, add to its product_images, an array also indexed by product_images['id'].

// To hold the final array
$output = array();
foreach ($original_array as $row) {

  // Storing these id values to make them easier to refer to...
  $id = $row['products']['id'];
  $pid = $row['product_images']['id'];
  $paid = $row['product_aliases']['id'];
  $pivid = $row['product_image_votes']['id'];

  // Create the product entry if it does not exist
  // and initialize arrays for product_images and product_aliases    
  if (!isset($output[$id])) {
    $output[$id] = array(
      'products' => $row['products'],
      // Initialize these to sub-arrays containing the first ones from this row
      // using the id as the array key
      'product_aliases' => array($paid => $row['product_aliases']),
      'product_images' => array($pid => $row['product_images'])
    );
    // Then add the first vote
    $output[$id]['product_images'][$pid]['product_image_votes'] = array();
    $output[$id]['product_images'][$pid]['product_image_votes'][$pivid] = $row['product_image_votes'];
  }
  // If it does exist already, append the alias if that does not exist, the image, the vote etc.
  else {
    // First add the alias if not already set
    if (!isset($output[$id]['product_aliases'][$paid])) {
      $output[$id]['product_aliases'][$paid] = $row['product_aliases'];
    }
    // Then add the image if not already set
    if (!isset($output[$id]['product_images'][$pid])) {
      $output[$id]['product_images'][$pid] = $row['product_images'];
    }
    // And nest in the image_votes
    if (!isset($output[$id]['product_images'][$pid]['product_image_votes'][$pivid])) {
      $output[$id]['product_images'][$pid]['product_image_votes'][$pivid] = $row['product_image_votes'];
    }
  }
}

There's a lot here, and it is likely I have syntax errors or missing ] somewhere. Good luck with it.

answered Jan 27, 2013 at 20:29

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Alessandro Minoccheri Over a year ago

no problem for syntax errors, think that into my query I have a lot of join, I have count the table to join into my query: 40 tables to parse.. OMG.. I thinked that there is a method or something to help me instead of parse all the results

Michael Berkowski Over a year ago

@AlessandroMinoccheri Well, this is where ORM's or frameworks like CakePHP are useful. The table relationships are defined in the object model, then you retrieve a product for example and just call methods to get its associated images, etc. Without a framework you have to do a lot of the work manually.

Alessandro Minoccheri Over a year ago

This query is done into cakephp but I have too many contain and make 5000 queries. After this question stackoverflow.com/questions/14487038/… I decide to write the query manually

Michael Berkowski Over a year ago

@AlessandroMinoccheri That is the trade-off then. The framework generates a lot of queries and is slow, or you build one fast query and must parse the results yourself.

Alessandro Minoccheri Over a year ago

Is an hard choise, but I think that build the query manually is more efficient. But with 40 tables to join I'll spend a lot of time to parse It. But if this is the only way I have to done it

|

Collectives™ on Stack Overflow

SQL join into join create a good array

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related