julian1828

<p>THIS PLUGIN IS IN HEAVY DEVELOPMENT!</p>
<p>Extend your site’s querying and sorting functionality using custom field values.</p>
<p>The following function registers queryable fields.<br />
    register_custom_queryable_field($fieldName, $options);</p>
<p>Simple Example:<br />
    register_custom_queryable_field(‘city’);<br />
(The above registers the custom field ‘city’ to be searchable using query variables)<br />
    http://www.yoursite.com/?city=Anahiem<br />
(displays posts which have a meta key ‘city’ and a value of ‘Anahiem’)</p>
<p>Advanced Example:<br />
    register_custom_queryable_field(“price”, array(“dataType”=>”numeric”));<br />
(The above registers the custom field “price” to be searchable as a number using query variables)<br />
    http://www.yoursite.com/?price=500<br />
(displays posts which have a meta key ‘price’ and a exact value of ‘500’)<br />
    http://www.yoursite.com/?price_min=200&price_max=800&order_by=price<br />
(displays posts, sorted by ‘price’, which have a meta key ‘price’ and a value between ‘200’ and ‘800’)</p>
<p>Available Options:</p>
<ul>
<li>dataType
<ul>
<li>text (default)</li>
<li>numeric (receive min/max query variables, see above example)</li>
</ul>
</li>
<li>order
<ul>
<li>If the user uses the “order_by” query variable then this option determines the order. Available options are “ASC” and “DESC”(default)</li>
</ul>
</li>
<li>compare
<ul>
<li>Compare method for text types. Defaults to ‘=’. Recommend ‘LIKE’</li>
</ul>
</li>
</ul>
<p>There is a “order_by” query var made available.  This is used by adding <code>&order_by=price</code> to the URL.  In this instance, the query would sort based on the “price” field only.</p>
<p>Note: This plugin does not alter any queries when in the backend.</p>


Custom Query Fields

Extend your site's querying and sorting functionality using custom field values.

PRESSMAN

Hiroshi Sekiguchi

kazunao

pmhirotaka

hommakoharu

<p>※ Table names have been changed since v2.0.Please delete the meta_json table and reactivate it in the options screen.</p>
<p>Are you having trouble with slow queries due to WordPress table structure?<br />
This plugin helps your site speed up using JSON type columns, one of the features of MySQL, and prevents complex queries while maintaining the flexibility of WordPress development.<br />
You can also add a virtual column in the configuration page and index the virtual column to make it even faster.You can also add a virtual column in the configuration page and index the virtual column to make it even faster.</p>
<h3>Features</h3>
<p>If a large amount of meta data is given to 1 post/user/comment and a complex meta_query is specified, a large number of inner joins will occur, which will slow down the execution of the query.<br />
The effect will be noticeable especially when the number of records is large.<br />
This plugin avoids complex joins and prevents slowdowns by storing all the meta data for 1 post/user/comment in a JSON type column in a custom meta table.<br />
The dedicated configuration page includes a check for group_concat_max_len, a function to measure consistency of meta table and its custom meta table,<br />
and the ability to add virtual columns, add a The ability to create indexes. Indexes on virtual columns to make it even faster.</p>
<h3>Actual Behavior</h3>
<ul>
<li>You can create and delete json tables and triggers and data for each json table on the configuration page.</li>
<li>Create virtual columns and indexes for the json table you created in the configuration page.</li>
<li>If you add a virtual column to the json table in the configuration page, the SQL is converted to face the virtual column.</li>
<li>Converts the SQL that WordPress auto-generates when using the default APIs of get_posts, Wp_Query, and etc. into a simple SQL that combines the posts/users/comment table with its own meta table.</li>
<li>The custom meta table automatically stores data using MySQL and MariaDB triggers.</li>
</ul>
<h3>Conversion Conditions</h3>
<ul>
<li>Both the value in meta_key and meta_value is specified.</li>
<li>If meta_compare_key is specified, only <code>=</code> or <code>EXISTS</code> are allowed.</li>
<li><code>suppress_filters</code> is false.</li>
</ul>
<h3>Custom meta tables generated by plugin</h3>
<p>The actual table names are prefixed with $table_prefix.<br />
* postmeta_beyond (for posts table)<br />
* usermeta_beyond (for users table)<br />
* commentmeta_beyond (for comment table)</p>
<h3>Columns in each table</h3>
<p>The actual table names are prefixed with $table_prefix.<br />
* postmeta_beyond<br />
    * post_id(int)<br />
    * json(json)<br />
* usermeta_beyond<br />
    * post_id(int)<br />
    * json(json)<br />
* commentmeta_beyond<br />
    * post_id(int)<br />
    * json(json)</p>
<h3>Example of storing data in JSON type column</h3>
<pre>{ "state": "Wisconsin", "city": "Winter" } </pre>
<h3>Results of Speed Measurement</h3>
<p>ex1 ) Create 100 posts and 50 meta data for each post. Combine them 9 times.</p>
<ul>
<li>
<p>postmeta_beyond</p>
<ul>
<li>Equal:0.04</li>
<li>IN:0.09</li>
<li>NOT IN:0.06</li>
<li>LIKE:0.03</li>
<li>NOT LIKE:0.03</li>
<li>BETWEEN:0.04</li>
<li>NOT BETWEEN:0.04</li>
</ul>
</li>
<li>
<p>postmeta</p>
<ul>
<li>Equal:4.72</li>
<li>IN:4.79</li>
<li>NOT IN:4.74</li>
<li>LIKE:0.64</li>
<li>NOT LIKE:0.66</li>
<li>BETWEEN:0.63</li>
<li>NOT BETWEEN:0.63</li>
</ul>
</li>
</ul>
<p>ex2 ) 10,000 posts, 1 meta per post. meta_key is category.<br />
      Add a virtual column(category) to postmeta_beyond and put an index,<br />
      and compare it to the case without adding a virtual column.</p>
<ul>
<li>
<p>postmeta_beyond(with virtual column)</p>
<ul>
<li>Equal:0.02</li>
<li>IN:0.35</li>
<li>BETWEEN:0.12</li>
<li>NOT BETWEEN:0.09</li>
</ul>
</li>
<li>
<p>postmeta_beyond(without virtual column)</p>
<ul>
<li>Equal:0.15</li>
<li>IN:1.92</li>
<li>BETWEEN:0.37</li>
<li>NOT BETWEEN:0.29</li>
</ul>
</li>
</ul>
<h3>Notes</h3>
<ul>
<li>You’ll need TRIGGER, SELECT, and UPDATE permissions for the target table and its columns to use the trigger feature of MySQL and MariaDB.</li>
<li>It makes changes to SQL only if <code>suppress_filters</code> is false.</li>
<li>This plugin sets 4294967295 to group_concat_max_len when it create an original table with JSON type column. You can change the number by using filter ‘beyond_group_concat_max_len’.</li>
</ul>


Beyond Wpdb

※ Table names have been changed since v2.0.Please delete the meta_json table and reactivate it in the options screen. Are you having trouble with slo …

Custom Query Fields vs Beyond Wpdb