How to show user toolbar in record view?

This is the issue. If you add ordering like

order by r.title desc

then you cannot

and r.id < $itemid

HOw will you know which title is next?

yes you are correct - if ordering is by title it wont work like I have it.

But nevertheless the prev|next would still work ok - its just the user wouldnt follow the ordering they came in with.

But in my case I dont really care at this stage :D

The user is in some category, and they can flip thru records - thats enough!

But back to your point...

To order by some column other than id (for example: title) then we must change query depending on order column.

So if order by is string I think something like this using strcmp() to sort will work:

$query = "(SELECT r.id rid FROM #__js_res_record r

inner join #__js_res_record_category c on c.record_id = r.id

where c.catid = $catid

and r.published = 1

and strcmp(r.title, $currentTitle) = -1

order by r.title desc

limit 1)

UNION

(SELECT r.id rid FROM #__js_res_record r

inner join #__js_res_record_category c on c.record_id = r.id

where c.catid = $catid

and r.published = 1

and strcmp(r.title, $currentTitle) = 1

order by r.title

limit 1)";

That query will get the row before and the row after the current row when ordered by title.

Some disclaimers:

I'm not sure about strcmp on non-mysql db

need some change on 'if' to set prev and next div

There's no index on title so question how efficient it will be in practice, but a quick test on my dev db (15000 rows) shows its probably workable.

I still havent got my head round the filters thing

To apply filters you need first make query with object

$query = $db->getQuery(true);

$query->selectr.id as rid');

$query->from('#__js_res_record');

$query->where("strcmp(r.title, $currentTitle) = -1")

...

Then you may path this query through model

$model = JModelLegacy::getInstance('Records', 'CobaltModel');

$model->section = $this->section;

$query = $model->where($query);

$query = $model->wherefilter($query);

$query = $model->wherecategory($query);

ok thanks for input

yes query object, and obviously code as is doing heavy db work in template so needs pushing back into j mvc.

But I can 'lash it up' to do what I need for now so I wont take it any further for time being.

And about query object: It may be that if I remember correctly joomla has some issue with orderby in union query (maybe?)

oh and I think maybe that should be 'union all' I think so

thanks

And about query object: It may be that if I remember correctly joomla has some issue with orderby in union query (maybe?)

You can do this

$sql = "($query1) UNION ($query2)";

@ETH000
@Sergey

Just noticed your discussion about prev/next navigation. Unfortunately, I did not notice it earlier, because I did 2nd level replies on comments for the initial topic. I think, that did not subscribe me to this topic :(

BTW, would have been easier if you would have had commented over here: ;-) Anyway, thanks for your thoughts!

Navigation - previous, next and up

the prev | next stuff I got into here was a continuation of my general question

point taken about the earlier specific thread, but I only just saw it yesterday

pepperstreet @ETH000

@Sergey

Just noticed your discussion about prev/next navigation. Unfortunately, I did not notice it earlier, because I did 2nd level replies on comments for the initial topic. I think, that did not subscribe me to this topic :(

BTW, would have been easier if you would have had commented over here: ;-) Anyway, thanks for your thoughts!

Navigation - previous, next and up

EDIT: I have updated the links to and from my C8 topic. We lost the "connection" since we have moved to new Cobalt powered forum.