1
Shiva
Block weight not ordering correctly
  • 2009/7/17 20:12

  • Shiva

  • Quite a regular

  • Posts: 280

  • Since: 2006/7/9 1


One of my websites is not ordering the blocks correctly. This is the SQL:

SELECT b . *  FROM tbl_newblocks btbl_block_module_link m WHERE m.block_id b.bid AND b.isactive =AND b.visible =AND m.module_id IN , -)  AND b.bid IN 204203202201191192184185234172141140137138139136116153908988919267645616015915815751504948464523323022922822620819519018339215214213212211221211109876543212322242232192102202001941931961981561552051541041035758189218239 )  ORDER BY b.weightb.bid LIMIT 0 30


The SQL works in the expected way, i.e. orders by weight when you remove the following part of the sql
AND b.bid IN 204203202201191192184185234172141140137138139136116153908988919267645616015915815751504948464523323022922822620819519018339215214213212211221211109876543212322242232192102202001941931961981561552051541041035758189218239 )


Otherwise it seems to ignore the ordering by weight altogether.

Can someone shine some light on this? Its core XOOPS code. So hopefully some one else already has experience with this?

p.s.
This page may provide some pointers:
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

2
Shiva
Re: Block weight not ordering correctly
  • 2009/7/22 21:12

  • Shiva

  • Quite a regular

  • Posts: 280

  • Since: 2006/7/9 1


Any thoughts on this?

The symptom of this problems is that when a user is logged on the block order is displayed correctly but for anonymous user the blocks are the wrong order.


3
Shiva
Re: Block weight not ordering correctly
  • 2009/7/22 22:23

  • Shiva

  • Quite a regular

  • Posts: 280

  • Since: 2006/7/9 1


The following is the SQL for registered users (Which works fine; and it orders by weight):
SELECT b . * FROM tbl_newblocks btbl_block_module_link m WHERE m.block_id b.bid AND b.isactive =AND b.visible =AND m.module_id IN , -) AND b.bid IN 218204203202201191192188187186184185178170169168167166171172173165151150149148147146145144143142141140137138139136116153908988919267646362616059562091601591581575150494846452331524443424039215214213212211222120191817234161514131211109876543212322242232192102202001941931961981561552051541041035758189231230229228226225195199239 ORDER BY b.weightb.bid LIMIT 0 30


The following SQL is for anon users (this does not work, and order by bid but not weight for some reason!):
SELECT b.* FROM tbl_newblocks btbl_block_module_link m WHERE m.block_id=b.bid AND b.isactive=AND b.visible=AND m.module_id IN (0,-1) AND b.bid IN (204,203,202,201,191,192,184,185,234,172,141,140,137,138,139,136,116,153,90,89,88,91,92,67,64,56,160,159,158,157,51,50,49,48,46,45,233,230,229,228,226,208,195,190,183,39,215,214,213,212,211,22,12,11,10,9,8,7,6,5,4,3,2,1,232,224,223,219,210,220,200,194,193,196,198,156,155,205,154,104,103,57,58,189,218,239ORDER BY b.weight,b.bid



This is a mystery to me! It must be something to do with the "AND b.bid IN (" part of the SQL that makes it fail. But I don't know what or why!

Login

Who's Online

257 user(s) are online (190 user(s) are browsing Support Forums)


Members: 0


Guests: 257


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits