XF 2.3 Reducing Queries to one?

[Robert9]

Can someone please tell me what and how I can do this to minimize the queries:

I am building a class that outputs a page. I fetch the titles of ten threads and then the individual posts from those threads.

These ten threads and posts are almost never changed.
My query looks something like this:
a) 1 query: Fetch threads with thread_id in [1...10]
b) 10 queries: Fetch posts from the individual threads
(Yes, could do this in one go, but I need an example to save a lot of queries)

So I have eleven queries. The result changes only once a year. How can I save this result so that I only need one query? Would I save the result as something in a field?

Which functions I can use to save the result?
What i do save? In which form?

In a finished version, i can do something:

If threads 1-10 are changed, set trigger to off
If page is called and trigger=off, do the queries, saave the result, set the trigger to yes, show the page.

As long the threads are not changed, i need one query fot the trigger and the result as a rwo in a table for example.

Are there any functions for this?
In which form I would save thread and posts, please?

 

[briansol]

IMO, change your approach.

Don't build a page that pulls from source..
Build a page that pulls from a cache table / record that is built from a job.

Build a cron job that does the queries.
Run a cron to update it once a day/etc.
Trigger the cron on content change.

 

[bzcomputers]

@Robert9 Plug your existing code and needs into chatgpt.com, and you may be surprised, in a couple seconds you may have exactly what you need. At worst it'll give you something you can tweak to fit your needs.

 

[Robert9]

Yes, that's one possibility.
Since I don't know better, I now see three options:

1. Cron job and fetch cache.
2. Call page, then fetch cache, check date, maybe fetch data, save data, and show data.
3. Job starts manually from ACP.

3. seems to be the best for my example, when the data is changed one time a year.
2. is better when more than one users edit, and we have no time to wait for cron
1. is better when more often or more users and we can wait for cron

But i see there's a problem with attachments!
This means I also have to save some information about attachments.

Ok, then this is the best solution?

I remember ... 2000 i have solved this with a written .txt and including it.

 

[Robert9]

ChatGPT has banned me until 2028 because I called him 1,024 times the most stupid idiot in the world.

In other words, ChatGPT talks a lot, is very helpful, but you never know if it's guessing, and you also don't know if you're getting the optimal solution.

I have many such questions; here’s an example:

Your threads should be sortable and filterable, here are the solutions:

1. custom_fields, yes for everything that needs to be filtered, but not for sorting.
2. new fields in xf_threads; perfect for everything, but it massively bloats the table because all threads will have these fields, not just the few thousand out of tens of thousands that need them.
3. a new table just for filtering on an external page, sort fields as new fields in xf_thread; custom_fields for displaying data; alternatively, display data in an array in the new table or in a new field in xf_threads.

If you managed to follow that, feel free to have a chat with ChatGPT about what the optimal solution is, when, and why. I can tell you, it won’t be fun, believe me!

 

[bzcomputers]

ChatGPT talks a lot, is very helpful, but you never know if it's guessing, and you also don't know if you're getting the optimal solution.

I agree.

The coding capabilities in the latest version released a couple weeks ago (o1) are considerably better. It is now much better writing and debugging complex coding tasks. I've still run into issues having to break down some multi-part tasks but the new version is a big step in the right direction. At a minimum it's still a huge time saver for the tedious things, and when coding in languages you don't normally code in.

 

[Robert9]

Yes, i have a preview and o1 mini; seen a minute ago.
I want to hit it like before!

 

[MySiteGuy]

If you need to turn a set of one-to-many queries into a single query, use a JOIN.

 

[Robert9]

This is not the point!
Of course I will limit queries.
But the question is how can i limit it to 1 or 0.

1 with a new table and some fields.
0 with a including a text field.

or maybe with writing a template?

 

[MySiteGuy]

This is not the point!
Of course I will limit queries.
But the question is how can i limit it to 1 or 0.

1 with a new table and some fields.
0 with a including a text field.

or maybe with writing a template?

It is exactly the point.
Using a JOIN properly will make it 1 query instead of 1+10. That's the primary purpose of a JOIN.

 

[Robert9]

Why exactly are you forcing a discussion on me that I don't want to have?
Do I now have to invent examples that specifically can't be combined in a query?

Why always so narrow-minded and with tunnel vision?
I want to find solutions for how I can cache something, and comments about JOINS are absolutely not helpful.

 

[MySiteGuy]

You're an adult, and no one is forcing you to do anything.

Good luck with your project.

 

[Miri]

Why exactly are you forcing a discussion on me that I don't want to have?
Do I now have to invent examples that specifically can't be combined in a query?

Why always so narrow-minded and with tunnel vision?
I want to find solutions for how I can cache something, and comments about JOINS are absolutely not helpful.

Sorry but I consider it a lack of respect. You should be more grateful to those who waste time answering your (my) questions.

 

[Robert9]

You want to talk about respect? Respect is when you carefully consider what someone else wants to know and accept what that person desires. Respect is also reading through an entire discussion thread to understand the situation before trying to talk about respect while being completely off-topic. To make this clearer for you, here is a typical example that can be found in hundreds of forums every day.

A: Hey, I want to buy a cat.

B: Yes, yes, but better buy a dog!

A: No, no, I want to have a cat.

B: Of course, but a dog would be better.

A: Why do you keep bringing this up? I want to have a cat!

B: Because a dog is better!

A: Why do you still keep bringing this up? I want to have a cat!

B: Because I know better than you what you need!

A: Go to hell!

 

[philmckrackon]

This is not the point!
Of course I will limit queries.
But the question is how can i limit it to 1 or 0.

1 with a new table and some fields.
0 with a including a text field.

or maybe with writing a template?

Why exactly are you forcing a discussion on me that I don't want to have?
Do I now have to invent examples that specifically can't be combined in a query?

Why always so narrow-minded and with tunnel vision?
I want to find solutions for how I can cache something, and comments about JOINS are absolutely not helpful.

You want to talk about respect? Respect is when you carefully consider what someone else wants to know and accept what that person desires. Respect is also reading through an entire discussion thread to understand the situation before trying to talk about respect while being completely off-topic. To make this clearer for you, here is a typical example that can be found in hundreds of forums every day.

A: Hey, I want to buy a cat.

B: Yes, yes, but better buy a dog!

A: No, no, I want to have a cat.

B: Of course, but a dog would be better.

A: Why do you keep bringing this up? I want to have a cat!

B: Because a dog is better!

A: Why do you still keep bringing this up? I want to have a cat!

B: Because I know better than you what you need!

A: Go to hell!

So much anger. I respectfully say, someone has lost my respect. Hope you figure out your query issue...

 

[Paul B]

Alienating and insulting those who are trying to help you probably isn't the best way to behave in a peer to peer support forum.

You may want to alter your approach.

Or not, your choice.

 

[MySiteGuy]

You want to talk about respect? Respect is when you carefully consider what someone else wants to know and accept what that person desires. Respect is also reading through an entire discussion thread to understand the situation before trying to talk about respect while being completely off-topic. To make this clearer for you, here is a typical example that can be found in hundreds of forums every day.

A: Hey, I want to buy a cat.

B: Yes, yes, but better buy a dog!

A: No, no, I want to have a cat.

B: Of course, but a dog would be better.

A: Why do you keep bringing this up? I want to have a cat!

B: Because a dog is better!

A: Why do you still keep bringing this up? I want to have a cat!

B: Because I know better than you what you need!

A: Go to hell!

I made my suggestion because a single query is pretty much zero added load, and solves worry about overhead very simply. If you want to complicate or overthink things, that's your call. Suggestion: If you didn't want to hear about reducing queries, you probably should not have titled the thread "Reducing queries to one"

I wish you no ill will, and hope your project goes as well.