https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42995
Tomás Cohen Arazi (tcohen) <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |[email protected] --- Comment #4 from Tomás Cohen Arazi (tcohen) <[email protected]> --- Hi folks. Sorry I missed this situation when doing bug 41950. Here's the full picture. When the page requests orders with biblio.items+count and biblio.suggestions.suggester: What the code builds (attributes passed to DBIC): ```perl { rows => 20, page => 1, prefetch => { biblio => ['suggestions', 'biblioitem'] }, '+select' => [ \"(SELECT COUNT(*) FROM items WHERE items.biblionumber = ???)" ], '+as' => ['biblio.items_count'], } ``` Without has_many in prefetch (e.g. only biblio which is belongs_to), DBIC generates a single flat query: ```sql SELECT me.*, (SELECT COUNT(*) FROM items WHERE items.biblionumber = biblio.biblionumber), biblio.* FROM aqorders me LEFT JOIN biblio ON biblio.biblionumber = me.biblionumber LIMIT 20 ``` Here `biblio.biblionumber` works fine because biblio is joined in the same query. With has_many in prefetch (suggestions or biblioitem are has_many from Biblio), DBIC uses a limiting subquery to avoid row multiplication from the has_many join: ```sql SELECT subq.*, biblio.*, suggestions.*, biblioitem.* FROM ( -- Inner query: pagination happens here, NO joins to biblio SELECT me.*, (SELECT COUNT(*) FROM items WHERE items.biblionumber = biblio.biblionumber) -- BOOM! FROM aqorders me LIMIT 20 ) subq LEFT JOIN biblio ON biblio.biblionumber = subq.biblionumber LEFT JOIN suggestions ON suggestions.biblionumber = biblio.biblionumber LEFT JOIN biblioitem ON biblioitem.biblionumber = biblio.biblionumber ``` DBIC moves `+select` into the inner query but puts the joins in the outer query. The subquery references `biblio.biblionumber` but `biblio` isn't joined there. MySQL says: `Unknown column 'biblio.biblionumber' in WHERE`. The fix is to use `me.biblionumber` instead: ```sql SELECT subq.*, biblio.*, suggestions.*, biblioitem.* FROM ( SELECT me.*, (SELECT COUNT(*) FROM items WHERE items.biblionumber = me.biblionumber) -- OK! FROM aqorders me LIMIT 20 ) subq LEFT JOIN biblio ON biblio.biblionumber = subq.biblionumber LEFT JOIN suggestions ON suggestions.biblionumber = biblio.biblionumber LEFT JOIN biblioitem ON biblioitem.biblionumber = biblio.biblionumber ``` `me.biblionumber` is always available in the inner query because me IS the inner query's table. And since `me.biblionumber` is the FK that points to `biblio.biblionumber`, it holds the same value. The correlation is semantically identical. -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
