Products increasing by number of available locations

For a particular product, I can have multiple locations. The problem I have having is that if I have one product and one location, it shows one product. But if I have 1 product and 2 locations, it shows that there are 2 products. If I have 1 product with 3 locations, it shows that there are 3 products etc. This isn't what should be happening. The product total should always be 1.

LEFT JOIN `prod_locations` as `pl` ON pl.`product_id` = bbp.`prod_id`

Open in new window


Removing the above line and removing it from the WHERE statement fixes the issue but I need that table in order to search all locations

		$now = date('Y-m-d');
		$this->db->query("SELECT `prod_name`,
		`start_date`, 
		`end_date`,
		`reg_price`,
		`sale_price`,
		bbp.`prod_id`,
		`vouch_limits`,
		`sp_name`,
		`order_id`,
		`img_name`,
		`outlets`,
		po.`outlet_id`,
		`product_slug`,
		`frontend`,
		COUNT(voucher_id) AS totalVouchers
         , SUM(CASE WHEN purchased = '0000-00-00' THEN 1 ELSE 0 END) AS TotalWithDate0000
		FROM `vouchers` as `v`
		INNER JOIN `bb_products` as `bbp` ON bbp.`prod_id` = v.`product_id`
        
		LEFT JOIN `prod_locations` as `pl` ON pl.`product_id` = bbp.`prod_id`
        
		INNER JOIN `service_providers` as `sp` ON sp.`service_provider_id` = bbp.`provider_id`
		INNER JOIN `prod_pics` as `pp` ON pp.`prod_id` = bbp.`prod_id`
		INNER JOIN (SELECT `product_id`, `outlet_id`, COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`
		WHERE `frontend` = 'yes'
		AND pp.`featured` = 'yes'
		AND bbp.`start_date` <= :now
		AND bbp.`end_date` >= :now
		AND (pl.`location_id` = :prod_location OR `prod_location_id` = :prod_location)
		GROUP BY bbp.`prod_id`
		LIMIT :limit
		OFFSET :offset
		");
		
		$this->db->bind(":prod_location", $data['location_id']);
		$this->db->bind("now", $now);
		$this->db->bind(":limit", $per_page);
		$this->db->bind(":offset", $offset);
		$results = $this->db->resultSet();
		return $results;

Open in new window

LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: Use tables alias names consequently in your query.

Then you need either an EXISTS predicate in the WHERE clause or you need to GROUP BY  / DISTINCT your result. EXISTS() can be in many situations faster than GROUP BY / DISTINCT.

E.g. something like

SELECT   prod_name ,
         start_date ,
         end_date ,
         reg_price ,
         sale_price ,
         bbp.prod_id ,
         vouch_limits ,
         sp_name ,
         order_id ,
         img_name ,
         po.outlets ,
         po.outlet_id ,
         product_slug ,
         frontend ,
         COUNT(voucher_id) AS totalVouchers ,
         SUM(CASE WHEN purchased = '0000-00-00' THEN 1
                  ELSE 0
             END) AS TotalWithDate0000
FROM     vouchers v
         INNER JOIN bb_products bbp ON bbp.prod_id = v.product_id
         INNER JOIN service_providers sp ON sp.service_provider_id = bbp.provider_id
         INNER JOIN prod_pics pp ON pp.prod_id = bbp.prod_id
         INNER JOIN (   SELECT   product_id ,
                                 outlet_id ,
                                 COUNT(DISTINCT outlet_id) AS outlets
                        FROM     prod_outlets
                        GROUP BY product_id ) po ON po.product_id = bbp.prod_id
WHERE    pp.frontend = 'yes'
         AND pp.featured = 'yes'
         AND bbp.start_date <= :now
         AND bbp.end_date >= :now
         AND EXISTS (   SELECT *
                        FROM   prod_locations pl
                        WHERE  pl.product_id = bbp.prod_id
                               AND (   pl.location_id = :prod_location
                                       OR prod_location_id = :prod_location ))
GROUP BY bbp.prod_id;
LIMIT :limit
OFFSET :offset

Open in new window

0
Black SulfurAuthor Commented:
Thank you. This is giving me the correct number now but is only showing 1 product result instead of the 3 I should see.
0
ste5anSenior DeveloperCommented:
D'oh??
If I have 1 product with 3 locations, it shows that there are 3 products etc. This isn't what should be happening. The product total should always be 1.
You wrote, that you want only one..?
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Black SulfurAuthor Commented:
This is a search function. So, it should show multiple results as in many search results but the quantity of the individual product should have been for example, 1, not only show 1 single product in the search results list.

Sorry for not being clear!
0
ste5anSenior DeveloperCommented:
hmm, it's not really gettin' better..

How does your model look like? What is the cardinality between products and locations - I assume bb_products and prod_locations?
0
Black SulfurAuthor Commented:
When you add a product, there are 2 dropdowns. One for the primary location which is a required field. Then there are secondary locations which are optional.

So, let's say I add 1 primary location and 2 secondary locations.

If I perform 3 separate searches and in search one I choose location one, it should also should up if I search location 2 or 3.

But it is multiplying the stock count by the number of secondary locations I have.

I just checked now, I have 3 in stock and have 2 secondary locations so it is showing I have 6 on hand when I in fact only have 3.

The required location is in bb_products and the secondary ones are in prod_locations as you mentioned which stores the product id and location id
0
Black SulfurAuthor Commented:
The strange thing is that this only happens when I search by primary location. If I search by secondary location it shows 3 in stock as it should. The plot thickens...
0
Black SulfurAuthor Commented:
Sorry, I forgot to add that the primary location is stored in bb_products and the secondary locations in prod_locations
0
ste5anSenior DeveloperCommented:
I asked for the model, which is the last sentence:

The required location is in bb_products and the secondary ones are in prod_locations as you mentioned which stores the product id and location id
This is not a correct normalized relational model. Location is an entity and belongs to its own table.

Thus the problem with your query. Also simplify the query to its core:

SELECT   bbp.*
FROM     bb_products bbp        
WHERE    bbp.start_date <= :now
         AND bbp.end_date >= :now
         AND ( 
            bbp.prod_location_id = :prod_location
            OR
            EXISTS (   SELECT *
                       FROM   prod_locations pl
                       WHERE  pl.product_id = bbp.prod_id
                              AND  pl.location_id = :prod_location
                                      )
            )
LIMIT :limit
OFFSET :offset

Open in new window


btw, the problem was: I couldn't identify your "primary location in the products table", because you didn't use tables alias names consequently.
0
Black SulfurAuthor Commented:
Yeah, I know it isn't ideal. But the additional locations were added as an afterthought by owner of the site and I had to work around it. Having their own table would have been better. But there is so much data already stored in the bb_products table.
0
ste5anSenior DeveloperCommented:
Well, did you tell that the owner of the site? Cause missing normalization means problem in performance and much worse redundancy and possible wrong data. E.g. the current model allows to have the same location as primary as well as secondary location. This can lead to wrong statistics. And when business process are driven by the count of locations, than they use wrong input values.

But for the correct location filter see the above post.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
I will have to somehow change it which will probably mean downtime but for now I just need something that works in the interim so the stock showing is correct. If that is not possible then I will have to just bite the bullet and change it. It doesn't seem like there is another solution, well one hasn't been posted yet.
0
ste5anSenior DeveloperCommented:
I posted that already, read the complete post.
0
Black SulfurAuthor Commented:
Thanks ste5an, I am trying to implement that with my joins as your code doesn't show the other tables that I need to join.
0
Black SulfurAuthor Commented:
I don't actually think that the issue is what we think it is.

I left off the bb_products table in the condition and it still happens. I changed:

AND (pl.`location_id` = :prod_location OR `prod_location_id` = :prod_location)

Open in new window


to

AND pl.`location_id` = :prod_location

Open in new window


So now it isn't even looking at the location in the bb_products table? Unless I am mistaken.
0
ste5anSenior DeveloperCommented:
*cough*

No. The first step is to verify, that the core query is correct. Then you implement the rest.
0
ste5anSenior DeveloperCommented:
The rectangle is the condition for the primary location in the products table. The blue the condition for the secondary locations in the location table:

Untitled.png
0
ste5anSenior DeveloperCommented:
I don't actually think that the issue is what we think it is.
In this case, you need to describe your data model.. cause I'm currently only guessing.
0
Black SulfurAuthor Commented:
Well, I first want to establish if what I am saying is true. This:

AND (pl.`location_id` = :prod_location OR `prod_location_id` = :prod_location)

Open in new window


checks both product_locations table and bb_products table

So, I changed it to:

AND pl.`location_id` = :prod_location

Open in new window


meaning that it should ONLY check the prod_locations table

Correct?
0
ste5anSenior DeveloperCommented:
No, step back and look at your model. You said, that the primary location is stored in the products table. Thus means - I guess:

SELECT   bbp.*
FROM     bb_products bbp
WHERE    bbp.prod_location_id = :prod_location

Open in new window

And then you have mentioned the secondary location, again I guessed:

SELECT   bbp.*
FROM     bb_products bbp
WHERE    EXISTS (   SELECT *
                    FROM   prod_locations pl
                    WHERE  pl.product_id = bbp.prod_id
                            AND  pl.location_id = :prod_location
                )

Open in new window

Thus when you search for all locations:

SELECT  bbp.*
FROM    bb_products bbp
WHERE   bbp.prod_location_id = :prod_location
    OR  EXISTS (   SELECT *
                    FROM   prod_locations pl
                    WHERE  pl.product_id = bbp.prod_id
                            AND  pl.location_id = :prod_location
                )

Open in new window

0
Black SulfurAuthor Commented:
Yes, the primary location is stored in the products table but I am trying to ignore that.

So, I changed the primary location to something else and added the original primary location to the secondary location.

So, let's say for arguments sake it looked like this:

Primary Location
USA

Secondary Locations
Brazil
Mexico

I now changed it to:

Primary Location
Norway

Secondary Locations
USA
Mexcio
Brazil

So, if I searched in Norway, the product should not show up because I am now ignoring the primary location in the bb_products table. The product should only show if I selected USA, Mexico or Brazil.
0
ste5anSenior DeveloperCommented:
Then you need to search only in the secondary locations...

SELECT   bbp.*
FROM     bb_products bbp
WHERE    EXISTS (   SELECT *
                    FROM   prod_locations pl
                    WHERE  pl.product_id = bbp.prod_id
                            AND  pl.location_id = :prod_location
                )

Open in new window

0
Black SulfurAuthor Commented:
I decided that the best thing would be to remove the location from the products table and only use the locations table for locations. It is more work but is the right thing to do. Thanks, ste5an.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.