Order by/Joins

Bros having some probs with this, so pointers greatly appreciated

this query brings back the wrong highest_bidder and highest_bidders_id which is brought from my bid and users table
if i do a simple query like this:

SELECT b.bid_id,
u.user_id, u.username, b.price
FROM bids b
LEFT JOIN users u ON u.user_id = b.user_id
ORDER BY b.price DESC,
         b.created_timestamp DESC,
        b.bid_id DESC
LIMIT 1

then i get the correct results as i should with this big query below.

SELECT i.item_id,
                     i.user_id,
			         i.title,
			         i.buy_now,
			         i.auction,
                     i.condition,
                     i.offers_accepted,
                     i.free_delivery,
                     i.free_delivery_condition,
                     i.collection,
                     i.collection_only,
	                 i.p_and_p,
                     i.buy_now_price,
                     i.quantity,
                     i.description,
                     (i.quantity - count(ip.item_id)) AS `quantity_available`,
                     COUNT(ip.item_id) AS `quantity_sold`,
                     io2.offer_price,
                     u.username AS `seller`,
                     DATE_FORMAT(u.timestamp, '%d/%m/%y') AS `member_since`,
                     @bid_increment := bi.increment AS `bid_increment`,
                     @current_auction_price := IF (b.price IS NULL, i.starting_price, b.price) AS `current_auction_price`,
                     TRUNCATE((@current_auction_price + @bid_increment), 2) AS `minimum_bid`,
                     ua.town_city,
                     ua.country,
                     ua.continent,
                     u2.username AS `highest_bidder`,
                     u2.user_id AS `highest_bidder_id`,
                     io2.quantity AS `quantity_offer`,
                     LOWER(c.country_code) AS `sellers_country_code`,
                     COUNT(DISTINCT io.item_offer_id) AS `offer_count`,
                     CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`,
			         ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`,
                     COUNT(DISTINCT b.bid_id) AS `bid_count`,
                     COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`,
                     COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`,
                     CONCAT_WS(', ', ua.town_city, ua.county, ua.country) AS `location`,
                     NOW() AS `server_time`,
			         @timestamp := CONVERT_TZ(DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY), '+00:00', '+00:00') AS `timestamp`,
                     DATE_FORMAT(@timestamp, '%D, %M at %h:%i %p') AS `end_timestamp`,
                     (SELECT COUNT(reported_item_id)
                      FROM reported_items
                      WHERE item_id = i.item_id
                      AND user_id = :user_id) AS `reported`,
                     (SELECT COUNT(item_id)
                      FROM user_item_wishes 
                      WHERE item_id = i.item_id
                      AND user_id = :user_id) AS `wished`,
                     (SELECT COUNT(item_id) 
                      FROM user_item_watches 
                      WHERE item_id = i.item_id
                      AND user_id = :user_id) AS `watched`,
                     (SELECT COUNT(user_follower_id) 
                      FROM user_followers
                      WHERE user_id = i.user_id
                      AND follower_id = :user_id) AS `followed`,
                     (SELECT COUNT(DISTINCT(ip_address)) 
                      FROM statistics 
                      WHERE DATE_ADD(visited, INTERVAL 2 HOUR) >= now()) AS `period_last_visited`,
                     (SELECT COUNT(DISTINCT(ip_address)) 
                      FROM statistics 
                      WHERE uri = '/item/1') AS `views`,
                      IF (i.start_timestamp >= now(), true, false) AS `ended`,
                      CASE WHEN (:latitude IS NOT NULL AND 
			                     :longitude IS NOT NULL AND
			                     u.latitude IS NOT NULL AND
			                     u.longitude IS NOT NULL)
			               THEN 
                                @distance := (SELECT (IF(:distance_unit = 'Kilometers', 6371, 3959) *
                                              2 * ASIN(SQRT(POWER(SIN((:latitude- u.latitude) *
                                              pi()/180 / 2), 2) + COS(:latitude * 
	                                          pi()/180) * COS(u.latitude * pi()/180) * 
	                                          POWER(SIN((:longitude - u.longitude) * 
	                                          pi()/180 / 2), 2)))))
			         END,
                     @distance_unit := IF (@distance >= 1, IF (@distance < 2, REPLACE (:distance_unit, 's', ''), @distance_unit), :distance_unit),
			         IF (@distance, CONCAT(TRUNCATE(@distance, 0), space(1) , @distance_unit), 'Unknown Distance') AS `distance`
			  FROM items i
			  LEFT JOIN users u ON u.user_id = i.user_id
              LEFT JOIN item_purchases ip ON ip.item_id = i.item_id
              LEFT JOIN user_item_wishes uiw ON uiw.item_id = i.item_id
              LEFT JOIN user_item_watches uiwa ON uiwa.item_id = i.item_id
              LEFT JOIN user_addresses ua ON ua.user_id = i.user_id
              LEFT JOIN item_addresses ia ON ia.user_address_id = ua.user_address_id
              LEFT JOIN countries c ON c.country_name = ua.country
              LEFT JOIN item_offers io ON io.item_id = i.item_id
              LEFT JOIN users_feedback uf ON uf.user_id = i.user_id
              LEFT JOIN item_offers io2 ON io2.user_id = i.user_id
              LEFT JOIN bids b ON b.item_id = i.item_id
              LEFT JOIN users u2 ON u2.user_id = b.user_id
              LEFT JOIN bid_increments bi ON b.price BETWEEN bi.price_from AND bi.price_to
              WHERE i.item_id = :item_id
              AND ia.user_address_id = i.user_address_id
              AND uf.seller = '1'
              ORDER BY b.price DESC,
                       b.created_timestamp DESC,
                       b.bid_id DESC

the joins and the order by are correct so i dont understand why the query is producing the wrong highest bidder if anyone know why please?

also any general advice on this query, how i should do things would be greatly welcomed.
thank you

I’ve half an idea I’ve read something about the order records are returned with a join like you’re doing. If you use the first query without the join, does it return the correct highest bidder id and bid from bids?

So what DO you get? Give us the row of the highest bidder, and the user row of the user that gets returned.

Query without the left join does bring back the correct results

Results from 1st query below, which are correct…
b.bid_id = 160881
b.price = 150.00
u.username = user1
u.user_id = 6

Second query results below are wrong…
b.bid_id = 1
u.user_id = 1
b.price = 100
u.username = ‘usertest’

So… I have to ask… why do you have a query that’s a page and a half long? Give me the one-sentence “This is what this query does”, cause… that’s a monster of a query that does things you really shouldnt be doing.

trick question! The query updates an auction page. What does it do that shouldn’t be done?

problem has been narrowed down to

  (i.quantity - count(ip.item_id)) AS `quantity_available`,
    COUNT(ip.item_id) AS `quantity_sold`,
    io2.quantity AS `quantity_offer`,
    COUNT(DISTINCT io.item_offer_id) AS `offer_count`,
    CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`,
    ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`,
    COUNT(DISTINCT b.bid_id) AS `bid_count`,
    COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`,
    COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`,

Mostly it’s about duplicate data retrieval…

i.quantity,
(i.quantity - count(ip.item_id)) AS quantity_available,
COUNT(ip.item_id) AS quantity_sold,

Why do i need to calculate quantity available if you’re returning the number sold and the quantity already? Do this in your code.

CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), ‘%’) AS sellers_feedback_percentage,
Division by zero possible.

NOW() AS server_time,
… why? is there expected to be a noticable difference between your database server and script server?

COUNT(DISTINCT uiw.user_item_wish_id) AS wish_count,

(SELECT COUNT(item_id)
FROM user_item_wishes
WHERE item_id = i.item_id
AND user_id = :user_id) AS wished,

Is this… not the same thing? Same with watched?

not my code, so cant comment on it too much. But incase any one faces a similar problem, below reply to same thread on phpfreaks seems to provide solution

Without a GROUP BY clause the aggregations will be for the whole table and non-aggregated fields will likely be whatever happened to be in those columns in the first record in the table.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.