Slow Query

Thanks for your question and for taking the time to find and include the SQL text of the query you are concerned about.

MLA uses the WordPress WP_Query class for these queries, and this is the SQL that WordPress generates.

I have developed some example code that substitutes other SQL for the WordPress queries. You can find these in the /media-library-assistant/examples/ directory:

alexa-paige-plugin.php.txt
mla-a-z-cloud-example.php.txt
mla-random-galleries.php.txt
mla-tax-query-example.php.txt

If you need a higher-performance query an alternative along these lines may work for you. If that’s of interest, I can be more specific after 2/17 when I return home. Thanks for your interest in the plugin.

Hi David,

I have used mla-tax-query-example.php.txt. It seems to be much faster however the query fetches the same images when in my post I use the following code.

[mla_gallery my_custom_sql="attachment_tag=soc" orderby="ASC" posts_per_page=20 link="file" mla_caption="{+cappion+}"]<!--nextpage-->[mla_gallery my_custom_sql="attachment_tag=soc" orderby="ASC" posts_per_page="20" offset="20" link="file" mla_caption="{+caption+}"]

I am so sorry for interrupting your holidays.

Thanks for your update and for the work you’ve put into finding and using the mla-tax-query-example.php.txt example plugin.

As I understand the shortcode text you posted (very helpful; thanks) you want to show two pages of gallery results with 20 images per page. Aside from a minor spelling error (mla_caption="{+cappion+}") it looks fine. However, the example plugin does not support the offset="20" parameter you are using for the second page of results. Here are some suggestions.

First, you can use the MLA “Support for Alternative Gallery Output, e.g., Pagination” features to get a more general paginated gallery. You can read about that in the Documentation tab. If the <!--nextpage--> parameter is really all you need, there are a couple of alternatives. You can use the paged parameter instead of offset, as described in the Documentation tab:

paged – number of page. Show the items that would normally show up just on page X. The “paged=current” parameter is useful for “paginated single posts” (i.e. posts that include the <!–nextpage–> Quicktag one or more times). Simply make two or more copies of your [mla_gallery] shortcode separated by the Quicktag and include the “paged=current’ in each copy. For true pagination it is better to use the “mla_paginate_current” parameter.

I believe you could also use the MLA pagination parameter, although I can’t test that on the road. Change offset="20" to mla_paginate_current=2.

I would try changing offset="20" to paged="current" first. I would also encourage you to experiment with the more flexible MLA pagination features as you have time and interest.

I hope the above suggestions get you the results you need. Let me know if you have any problems or further questions regarding the pagination support in the example plugin.

Hi David,

Please accept my apologies for bothering you on your vacations.

I edited the shortcode as you suggested

Shortcode 1 (Unfortunately this does not help me in my efforts to load each page and then sort the images in a Masonry Style)

[mla_gallery my_custom_sql="attachment_tag=soc" orderby="date" order="DESC" posts_per_page=20 mla_paginate_current=1 link="file" mla_caption="{+caption+}"]
[mla_gallery my_custom_sql="attachment_tag=soc"  orderby="date" order="DESC" posts_per_page="20" mla_paginate_current=2 link="file" mla_caption="{+caption+}"]'

Shortcode 2
[mla_gallery my_custom_sql="attachment_tag=soc" orderby="date" order="DESC" posts_per_page=20 paged="current" link="file" mla_caption="{+caption+}"]<!--nextpage-->[mla_gallery my_custom_sql="attachment_tag=soc" orderby="date" order="DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]'

The second shortcode works fine with my above issues. It decreased the query time a lot and the images are loaded just fine. However I would like the gallery to start from the newest images uploaded.

What it does now it starts loading the older ones and the per page it sorts them to show from the newest to the oldest.

I need the system to load the newest first and then the oldest.

Once more I apologise for any inconvenience I caused.

Regards,

Onisiforos

Thanks for your updates and progress reports, and please be assured that it is not an inconvenience at all.

I regret that the current version of the example plugin will not support the sort order you need. To sort by date requires access to the posts database table and this would defeat the performance gains made possible by the alternative SQL query used in the example plugin.

There may be another approach and I am happy to investigate further as soon as I return home on February 17. Thanks for your understanding and you patience while I am travelling.

Thank you for your patience during my travels. I have returned home and done some further investigation and testing with my development system.

I am pleased to report that, contrary to my earlier post, the current version of the example plugin will do what you require. To sort the results to “start from the newest images uploaded” you can simply move the orderby= and order= parameters inside the my_custom_sql= parameter, i.e., your second shortcode should be:

[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page=20 paged="current" link="file" mla_caption="{+caption+}"]<!--nextpage-->[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]'

I am working on a new version of the example plugin that may have further performance improvements, but the version you already have should work if you make the suggested changes. Give it a try and let me know if it works for you.

Hi David,

I have used the shortcode as you suggested. However, duplicates the first 20 photos and the last 20 photos leaving the rest out.

Thank you very much for your help so far.

Thanks for your ongoing efforts and for your update. I am having a little trouble understanding what you mean by “duplicates the first 20 photos and the last 20 photos leaving the rest out.” Just to confirm, you have moved all the sorting parameters into the my_custom_sql parameter like this:

[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page=20 paged="current" link="file" mla_caption="{+caption+}"]
<!--nextpage-->
[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page=20 paged="current" link="file" mla_caption="{+caption+}"]

If you have 40 or fewer photos in the category you should see the most recent photos on the first “page” and the older photos on the second page. If there are more than 40 photos in the category you will see incomplete results. That is why I encouraged you to explore the MLA “Support for Alternative Gallery Output, e.g., Pagination” features to get a more general paginated gallery.

If I have not understood your comment about “duplicates … first … last” any additional details or examples you can provide would be helpful.

Hi David,

I am so sorry for such a poor description.

What I use is this

[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]
<!--nextpage-->
[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]
<!--nextpage-->
[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]
<!--nextpage-->
[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]
<!--nextpage-->
[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" paged="current" link="file" mla_caption="{+caption+}"]

Thank you for posting the source text of your shortcodes. I have tested the shortcodes you provided and, on my system, they are working properly. I get all of the items assigned to the attachment_tag term, page by page, in descending order without duplicates.

I also tested this alternative, which uses MLA pagination:

[mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" link="file" mla_caption="{+caption+}"]

Pages: [mla_gallery my_custom_sql="attachment_tag=soc orderby=date order=DESC" posts_per_page="20" mla_output=paginate_links]

These two shortcodes replace all of the shortcodes in your example. They give the same pagination without any extra, empty pages and they automatically adapt to the varying number of items assigned to the term. I encourage you to try this alternative approach for your application.

If you are still having trouble with duplicate items, let me know.

It has been a month since the last post in this topic. I assume you have found a solution to the paginations issues for your application.

I am marking this topic resolved, but please update it if you have any problems or further questions regarding the topic. Thanks for your interest in the plugin.