Geek Answers Handbook

Limit SQL connection when using CodeIgniter Active Record Class

I get a list of products. Each product can have 1 or more images, I only want to return the first image.

$this->db->select('p.product_id, p.product_name i.img_name, i.img_ext'); $this->db->join('products_images i', 'i.product_id = p.product_id', 'left'); $query = $this->db->get('products p');

Is it possible to restrict the db-> join entry to 1 using the active record class CI?

+4
source share
4 answers

Add $this->db->limit(1); before calling $this->db->get('products p'); . See Documents at ellislab.com : find the limit page.

EDIT: I misunderstood that you were trying to apply LIMIT to an internal JOIN statement.

No. Since you cannot do LIMIT in an internal JOIN statement in regular SQL, you cannot do this with the Code Igniter ActiveRecord class.

+1
source

You can achieve what you want using $this->db->group_by with left join:

 $this->db->select('products.id, products.product_name, products_images.img_name, products_images.img_ext'); $this->db->from('products'); $this->db->join('products_images', 'products_images.product_id = products.id', 'left'); $this->db->group_by('products.id'); $query = $this->db->get();

This should give you the results of products.id (without repeating the products), with the first matching record from products_images joining each row of the result. If there is no corresponding row in the joined table (i.e., if there is no image), you will get zero values ​​for the products_images fields, but you will still see the result from the products table.

0
source

To extend the answer to @Femi:

There is no good way to limit JOIN , and in fact, you really don't want to. Assuming that both products_image.product_id and products.id have indexes (and they must be, if you intend to join them again) when the database engine makes the connection, it uses indexes to determine which rows to retrieve. The engine then uses the results to determine where to find the records on the disc. If you

You can see the difference by running these SQL statements:

 EXPLAIN SELECT p.product_id, p.product_name, i.img_name, i.img_ext FROM products p LEFT JOIN products_images i ON i.product_id = p.product_id

Unlike:

 EXPLAIN SELECT p.product_id, p.product_name, i.img_name, i.img_ext FROM (SELECT product_id, product_name FROM products) p LEFT JOIN (SELECT img_name, img_ext FROM products_images) i ON i.product_id = p.product_id

The first request must have an index, the second - no. There should be a difference in performance if there is a significant number of rows in the database.

0
source

If this problem was also solved, it would repeat the results and delete the current object if product_id existed in the previous one. Create an array, click product_id on it, checking if they are repeated.

 $product_array = array(); $i = 0; foreach($result as $r){ if(in_array($r->product_id,$product_array)){ unset($result[$i]); }else{ array_push($product_array,$r->product_id); } $i++; } $result = array_values($result); //re-index result array

Now $ result is what we want

0
source

More articles:


All Articles