XF 2.2 Search for threads with some values?

[Robert9]

An example for the year 2026

100.000 threads
10 search "objects" in three forums with different "objects" to search.

Threads in forum one have colors like "blue, green, red"
Threads in Forum two have numbers like "10, 20, 30"
Threads in Forum three have mixed values like "blue, red, 20, 30"

Values could be checkboxes and radios or integers like year of production "1964"

1. I add ten new fields to xf_thread, fields are indexed and save integers.
select from where field1 == 1 AND/OR field3==3

1b.) same/same, but I select where field1 = "red" <= use char or enum?

1c. I can use bitfields to sum some values
1=red, 2=blue,4=green;
If I need red, blue, I can search for "3"
If I need blue and green, I can search for 7
Integer, indexed, best solution?

2. I can use custom_fields with one value, to select from custom_field_values with
select where field1 == "s1:1"; (something equal like this)

3. I can use checkboxes to search again in custom_field_values with
select where field1 like "s:1%1%" (something like that!)


From my point of view as someone who knows nothing:

4. Worst solution to search in textfield with like %text%
3. search in custom_fields_values with == on short columns, but many of them! for every thread you have maybe 5-10 values, then join threads?
2. indexed integers in the main table? but i give every thread ten new fields?! If used or not? Good idea?
1. indexed integers in the main table, but I reduce the needed fields by bitfields

Now, please, tell me, what should I do?

Hey, your website has 100 uniques per day, why do you think about this? Use custom_fields with checkboxes!
Hey, dont make yourself problems! If you have these numbers of users, buy a bigger server and don't care!

Hey, if you can solve this with bitfields just do! It is the best solution!

Hey, make it more complicated! Use new tables for your forums!
Forum 1 needs thread_id, blue, red, green => can be a bitfield! => thread_id, color_code
Forum2 needs some numbers! You can also make bitfields! => thread_id, number_code
Forum3 needs a combination! You can make two fields! => thread_id, number_code, color_code

Forum1, search in table1
Forum2, search in table2
Forum3, search in table3
and left join your threads!

Hey, this is ********! Use bitfields in the main_table! thread_id is anyway there, no join! Just add number_code, color_code;


The real world example?

none to eight colors! Could by 256 bits! Easy doing for green and red and blue = 1+2+4 = 7; but what we do with green or red or blue? 1 or 2 or 3 or 4 or 5 or 6 or 7 or just <=7; but will not help with 1+4+16; then you need = x or y or z; better to ask for one field if one or more of three values or select for three fields? (here 8!)
Year of production
Year for "produced since"
status 1 or 2 or 3
price1
price2
kg, length, height, width
text1 *
text2 *
text3 *
* searchable for stuff only
Another thread nobody can follow? I am sorry, it is sooooo clear! You don't think so.

 

[Paul B]

Another thread nobody can follow?

^ That.

It's really difficult to understand what you mean most of the time.

 

[Robert9]

My English seems not to be very helpful, sorry.

---

I want to use threads for products, and need some more values to save and search for.
I have no need to sort. I dont want to have another main table like RM, QB, Articles, whatever. I want to solve it with threads.

0-8 colors
Year of production
Year (produced since)
status 1 or 2 or 3
price1
price2
kg, length, height, width
-----------------------------
text1 *
text2 *
text3 *
* searchable for stuff only



There are several ways to solve this

1.
in the main table
xf_thread.field1 ... xf_thread.field10

2.
with custom_fields and search for custom_field_values with like %value%

3.
in a new table and join threads

4. Instead of saving some specified values for colors for example, I can save an integer added from bits.
(red=1, blue=2, green=4) I can save 7 instead "red,blue,green" or fields: red=1, blue=1, green=1 or fields color1=red, color2=blue, color3=green


Several add-ons use several ways to solve this.
To sort threads, people normally use a field in xf_thread.
For filters, people use all three ways.

---

My question is: How I should do my filters?
What is the best way* to solve this.

* Best way is relative, for ten users, then threads and one extra field i can use a custom_field and be finished in ten minutes.
but what to do for 10.000 users a day, 100.000 threads and 10 extra data fields?

Where can I learn from which numbers a solution must be improved?
(like to have different search value tables for different forums because less columns and less rows, when there a different attributes to search for.
Example:
forum1 for flowers, the can be red, blue, green
forum2 for cars, they can be new, old
forum3 for humans, they have kg, size, age

I can use eight custom_fields for this, and search in custom_fields_value
I can use eight new fields in xf_thread
I can use three new fields in xf_thread with bit-combination
I can use one extra-table
I can use three extra tables (will not do here, but is a way to solve it)


But maybe I am wrong with my idea to keep things simple and use only threads.
Maybe threads should be threads (XF) and products should be products (like RM, QB, SR, Articles ...)

 

[Robert9]

I try more examples. Let's say that the RM needs to be only a downloadlink.
Why we should use the RM as add-on? We add one more field to xf_threads and save the link.

Let's say that the RM needs to be two more fields like free, paid.
Again we can use xf_threads with one more field: paid: 0/1

on the other end we have the RM as an add-on, because there are updates, comments, rating ... so we have an add-on like the RM.


But where is the point to decide this? I just have

0-8 colors
Year of production
Year (produced since)
status 1 or 2 or 3
price1
price2
kg, length, height, width
-----------------------------
text1 *
text2 *
text3 *
* searchable for stuff only

And I can solve it with threads. But should I?
And if so, how I save my fields?

 

[Paul B]

From what I understand you are talking about the database schema.

How to configure that depends on many factors and ultimately comes down to query efficiency.

When I was writing my own add-ons I did multiple configurations and testing to determine what worked best.

 

[Robert9]

Yes, sure. And there are many things to think about:

sort, filter, search?

data stand-alone, with a connected thread (like RM)
or data to show only (like custom_fields for threads)
...

this is what I am talking about. Where to put the new fields?

1. main table (xf_thread)
2. custom_fields for threads (with select ... LIKE "%something%"
3. new table to search/filter only in this table (and have a connected thread)
4. new table to search/filter only and JOIN threads.


but another point of view is:

People don't understand that there are other areas in a forum.

For adding products they have to use the add-on show room (as example)
For adding quotes they have to use Quotebook (as example, is almost like RM without update-table)
For adding downloadable pdf we use the RM (as example)

But maybe it is just better to do all these things just in threads?

For products go to forum products; add thread, fill form with data for product, upload a picture.
For quotes go to forum quotes, add a thread, fill form with quote.
For pdfs go to forum pdfs, add a thread, fill a form and upload pdf.