XF 2.2 Solve the custom fields problem?

[Robert9]

You have a subforum with cats;
your cats have an age, a weight, a size, and some more integer values to know, filter and search.

I want to see all cats two till three years old with a weight between x and y kilo and a size of max z.

We can buy the monster add-on of addonslab.
We dont use an add-on that adds all values in an array and starts searching there (if it still exists)
We can try the add-on from ozz

or we can add our fields to xf_table; many fields, i dont like it.
Though we could add some fields with 0/1 to a byte_field to save fields


And we can do this new idea, i had a minute ago:

we extend the field_value_tables with a field integer_value and let xf know that it is used.
here we add an index now.

and now we could search in the custom_fields that are integers like

field_name=birth, find int_value > 18 and < 88
field_name=kilogramm, find int_value > 2 and < 4


Maybe we add a second field for enums and short text like "red, blue, grey"

Would this help to have better search and filter on custom-fields with such a relation, when the fields we use have an index?

        $structure->relations += [
            'FieldValue' => [
                'entity' => 'XF:ThreadFieldValue',
                'type' => self::TO_ONE,
                'conditions' => 'thread_id',
            ],
        ];

 

[Robert9]

I try it in other words:

You need new fields for your threads.
Integers, radios, checkboxes

You want to sort and you want to filter threads with theses values.


Solution1:
Add new fields to xf_threads; fastest way, we just select from xf_thread.
but maybe you add ten new fields for tenthousands of threads, while you need these values only for some hundreds of threads?!


Solution2:
Add custom_fields
plus the monster add-on from Addonslab (i guess they add new tables for the values to search in these table, then fetch threads or left join threads)
or
plus the add-on from ozzy
here we have to look up for
threads plus all custom_fields used for!
if we have ten fields, we need to select 11 items?
thread + field1 ... field 10?


Solution3:
You add a new table like
thread_id value1 value2 value 3

Now we could search in this table only, get the thread_ids and show them?
or left join the threads



But what is the best solution for which scenario?

Here is a real world scenario:

1000 threads with 5 new fields, will grow maybe by 500 per year. (node_id=1)*
5000 threads with 10 new fields, will grow by 500 till 1000 per month! (node_id=2)*
Normal discussions, maybe grow by 10 per day. (some node_ids)

(*the fields for 1 and 2 can be shared; means i need ten new fields, most of them tiny_ints with values from 0-4);
some as integers with a year (1234) or (123) or (12); some as an id growing like the thread_id (123456)

Maybe here it would be worth to add the fields directly to xf_table?
Or it doesnt matter, because we talk about 15.000 threads per year, and this number is a small number?

 

[Robert9]

I understand now? that ozzys solution is ok, when we use only one field;
than we select from thread left join one field.

I guess this is no solution, if we use ten fields to filter.

But here i have another question about queries:

Lets say we but all the field values in one table.
Then we filter like that:

color red or blue
year of production > 2020
price between 10k and 20k

with threads updated in the las two month sort by count_reply


Do we care about
select from xf_thread left join search_table or
select from search_table left join xf_thread?

I dont know how mysql works here.

I know that we maybe have 50 threads not older than wanted.
I know we have 1000 threads in the search_table
I know we have 10.000 threads

Do we care for what we search?
Does mysql makes a difference
select from A left join B
select from B left join A

maybe it is both the same like one big table?