[MediaWiki-l] Another query question...

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[MediaWiki-l] Another query question...

Jan Steinman
MySQL 5.0.92-log

I'm trying to form a clickable link using CONCAT, but the link as displayed points to the NEXT row's URL, not the one from the same row as the other data displayed!

Is there something I don't understand about this?

Below is the query. "{{{1}}}" is replaced by a year, like "2013".

The second column is the problem one. When the table is displayed, the link in the `Product` field points to the NEXT SEQUENTIAL product row! In other words, if you click on the link for "garlic," you'll get the page for "gherkins."

Live example is at: http://www.EcoReality.org/wiki/2013_harvest

If you hover over the link in the `Product` column, you can clearly see that the page at the link is not the same as that in the `ID` column, but is in fact the same `ID` as the next sequential row. I am so confused.

SELECT
    harvest.product AS ID,
    CONCAT('<a href="http://www.EcoReality.org/wiki/Product/', s_product.ID, '">', COALESCE(s_product.name, 'TOTAL:'), '</a>') AS `Product`,
    FORMAT(sum(harvest.quantity), 3) AS `<a href="http://www.EcoReality.org/wiki/Harvest">Harvest</a>`,
    harvest.units AS Units,
   CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
    prices.market_type AS `R-W`,
    COUNT(*) AS Harvests,
    DATE(MIN(harvest.date)) AS Begin,
    DATE(MAX(harvest.date)) AS End
FROM
    s_product_harvest harvest
  INNER JOIN
    s_product on s_product.ID = harvest.product AND
    s_product.units = harvest.units
  LEFT OUTER JOIN
    s_product_market_prices prices ON prices.product_ID = harvest.product AND
    prices.units = harvest.units AND
    year(prices.price_date) = year(harvest.date)
WHERE
    year(harvest.date) = {{{1}}}
GROUP BY
    s_product.name WITH ROLLUP

:::: Some days I wonder if it might not be better to culturally engineer humans to enjoy small scale garden farming than to genetically engineer weeds to save large scale agribusiness. -- Gene Logsdon
:::: Jan Steinman, EcoReality Co-op ::::


_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Another query question...

Jan Steinman
The plot thickens...

I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses " WITH ROLLUP", and removing this makes them behave as expected.

Is this a known bug? Should I submit it as such?

If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem.

Jan

Begin forwarded message:

> From: Jan Steinman <[hidden email]>
> Date: 3 November 2013 18:35:47 PST
>
> MySQL 5.0.92-log
>
> I'm trying to form a clickable link using CONCAT, but the link as displayed points to the NEXT row's URL, not the one from the same row as the other data displayed!
>
> Is there something I don't understand about this?
>
> Below is the query. "{{{1}}}" is replaced by a year, like "2013".
>
> The second column is the problem one. When the table is displayed, the link in the `Product` field points to the NEXT SEQUENTIAL product row! In other words, if you click on the link for "garlic," you'll get the page for "gherkins."
>
> Live example is at: http://www.EcoReality.org/wiki/2013_harvest
>
> If you hover over the link in the `Product` column, you can clearly see that the page at the link is not the same as that in the `ID` column, but is in fact the same `ID` as the next sequential row. I am so confused.
>
> SELECT
>    harvest.product AS ID,
>    CONCAT('<a href="http://www.EcoReality.org/wiki/Product/', s_product.ID, '">', COALESCE(s_product.name, 'TOTAL:'), '</a>') AS `Product`,
>    FORMAT(sum(harvest.quantity), 3) AS `<a href="http://www.EcoReality.org/wiki/Harvest">Harvest</a>`,
>    harvest.units AS Units,
>   CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
>    prices.market_type AS `R-W`,
>    COUNT(*) AS Harvests,
>    DATE(MIN(harvest.date)) AS Begin,
>    DATE(MAX(harvest.date)) AS End
> FROM
>    s_product_harvest harvest
>  INNER JOIN
>    s_product on s_product.ID = harvest.product AND
>    s_product.units = harvest.units
>  LEFT OUTER JOIN
>    s_product_market_prices prices ON prices.product_ID = harvest.product AND
>    prices.units = harvest.units AND
>    year(prices.price_date) = year(harvest.date)
> WHERE
>    year(harvest.date) = {{{1}}}
> GROUP BY
>    s_product.name WITH ROLLUP
>
> :::: Some days I wonder if it might not be better to culturally engineer humans to enjoy small scale garden farming than to genetically engineer weeds to save large scale agribusiness. -- Gene Logsdon
> :::: Jan Steinman, EcoReality Co-op ::::
>

:::: The competition for grain between the wealthy car drivers of the world and the poorest people who are trying to survive is a moral issue that we should not ignore. The continued increase in biofuels production will result in a continued decrease in food availability, which we could someday consider to be a crime against humanity. -- Pat Murphy
:::: Jan Steinman, EcoReality Co-op ::::


_______________________________________________
MediaWiki-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l