SOLVED!. New type of custom field: number input and interval filter

[marius-ciclistu]

Can anyone point me in the right dirrection to build a new type of custom field that is a number input field in add/edit item and an interval input field in filter search-sidebar, like the price is?

Thank you.

[marius-ciclistu]

Should I modify the existing text custom field or should I add a new one ?

[marius-ciclistu]

I need some help understanding this

In oc-includes/osclass/controller/search.php
There is

                            case 'DATEINTERVAL':
                                if( is_array($aux) && (!empty($aux['from']) && !empty($aux['to'])) ) {
                                    $from = $aux['from'];
                                    $to   = $aux['to'];
                                    $start = $from;
                                    $end   = $to;
                                    $sql = "SELECT fk_i_item_id FROM $table WHERE ";
                                    $sql .= $table.'.fk_i_field_id = '.$key.' AND ';
                                    $sql .= $start." >= ".$table.".s_value AND s_multi = 'from'";
                                    $sql1 = "SELECT fk_i_item_id FROM $table WHERE ";
                                    $sql1 .= $table.".fk_i_field_id = ".$key." AND ";
                                    $sql1 .= $end." <= ".$table.".s_value AND s_multi = 'to'";
                                    $sql_interval = "select a.fk_i_item_id from (".$sql.") a where a.fk_i_item_id IN (".$sql1.")";
                                    $this->mSearch->addConditions(DB_TABLE_PREFIX.'t_item.pk_i_id IN ('.$sql_interval.')');
                                }


What is the meaning of "a"  there?
Thank you.

edit.
PS. I tested something like
SELECT a.CustomerName FROM Customers a;
and
SELECT CustomerName FROM Customers;

and the results are the same.

[teseo]

Hi,

That's a subquery:

https://www.w3resource.com/mysql/subqueries/index.php

Subqueries are evaluated first and their results are stored in a pseudo-table (in this case named "a") so the main query may operate with them.

PS. I tested something like
SELECT a.CustomerName FROM Customers a;
and
SELECT CustomerName FROM Customers;

and the results are the same.

In this case you're replacing real name of the table "Customers" with an alias "a" (SELECT a.CustomerName FROM Customers AS a)

Regards

[marius-ciclistu]

Thank you Teseo.
So the correct thing would be

Code: [Select]

$sql_interval = "select a.fk_i_item_id from (".$sql.") AS a ";
Instead of

Code: [Select]

$sql_interval = "select a.fk_i_item_id from (".$sql.") a ";

OR taking into account that alias is not needed anymore:

$sql_interval = "select fk_i_item_id from (".$sql.")"; ?

[teseo]

$sql_interval = "select fk_i_item_id from (".$sql.")";

No, in the real case you're working on, "a" is not an alias but the name of that "pseudo-table", so it's mandatory.

$sql_interval = "select a.fk_i_item_id from (".$sql.") AS a ";

and

$sql_interval = "select a.fk_i_item_id from (".$sql.") a ";

Are both correct ("AS" can be omitted)

[marius-ciclistu]

Thank you. That point is resolved....
I started modifying the script by adding a NUMERIC option after the DATEINTERVAL.
It's more complex then I thought

Steps I've made so far:


1.
oc-includes/osclass/installer/struct.sql
I put

Code: [Select]

    e_type ENUM('TEXT','TEXTAREA','DROPDOWN','RADIO','CHECKBOX','URL', 'DATE', 'DATEINTERVAL','NUMERIC') NOT NULL DEFAULT  'TEXT',   
instead of
   

Code: [Select]

    e_type ENUM('TEXT','TEXTAREA','DROPDOWN','RADIO','CHECKBOX','URL', 'DATE', 'DATEINTERVAL') NOT NULL DEFAULT  'TEXT',
AND I MANUALY edited "t_meta_fields" in DB to contain also 'NUMERIC'

2.
oc-includes/osclass/controller/search.php

I put EDITED I must convert to number with CAST(string AS SIGNED) in order to compare and I removed the a. line because is useless.

Code: [Select]

                                    $this->mSearch->addConditions(DB_TABLE_PREFIX.'t_item.pk_i_id IN ('.$sql_interval.')');
                                }
                                break;
                            case 'NUMERIC':
                                if( is_array($aux)) {
                                    if(!empty($aux['to']) && empty($aux['from'])) $aux['from'] = '-1000000';
                                    if(!empty($aux['from']) && empty($aux['to']))  $aux['to'] = '10000000';
                                    $from = $aux['from'];
                                    $to   = $aux['to'];
                                    $start = Search::newInstance()->dao->escape($from);
                                    $end   = Search::newInstance()->dao->escape($to);
                                    $sql = "SELECT fk_i_item_id FROM $table WHERE ";
                                    $sql .= $table.'.fk_i_field_id = '.$key.' AND CAST(';
                                    $sql .= $start." AS SIGNED) <= CAST(".$table.".s_value AS SIGNED) AND CAST(".$end." AS SIGNED) >= CAST(".$table.".s_value AS SIGNED)";
                                    $this->mSearch->addConditions(DB_TABLE_PREFIX.'t_item.pk_i_id IN ('.$sql.')');
                                }
                                break;instead of

Code: [Select]

                                    $this->mSearch->addConditions(DB_TABLE_PREFIX.'t_item.pk_i_id IN ('.$sql_interval.')');
                                }
                                break;

3. This is not needed -> REMOVED
oc-includes/osclass/model/Search.php


4
oc-includes/osclass/frm/Field.form.class.php

I put

Code: [Select]

                <option value="DATEINTERVAL" <?php if($field['e_type']=="DATEINTERVAL") { echo 'selected="selected"';};?>><?php _e('DATE INTERVAL'); ?></option>
                <option value="NUMERIC" <?php if($field['e_type']=="NUMERIC") { echo 'selected="selected"';};?>><?php _e('NUMERIC'); ?></option>               
instead of

Code: [Select]

                <option value="DATEINTERVAL" <?php if($field['e_type']=="DATEINTERVAL") { echo 'selected="selected"';};?>><?php _e('DATE INTERVAL'); ?></option>
and

Code: [Select]

                foreach($aCustomFields as $field) {
                    if($field['e_type']=='DATEINTERVAL' || $field['e_type']=='NUMERIC') {instead of

Code: [Select]

                foreach($aCustomFields as $field) {
                    if($field['e_type']=='DATEINTERVAL') {                   


Any other suggestion that may ease may work are welcomed.

[marius-ciclistu]

Removed as without importance

[teseo]

It's more complex then I thought

Yes, I think so too (and you need to work on admin side so Admin may create such custom fields). I'm afraid I can't help you with the whole project.

Regards

[marius-ciclistu]

Nothing is impossible. I'll get it done for sure. In comparison with the mods I've made so far to this script this is not so big of a deal.

The idea is that in db nothing in structure will change, except the value "NUMERIC" added manually in "t_meta_fields" in e_type.
Then the meta field NUMERIC acts as a TEXT except when filtering. Then it must act a litle as DATEINTERVAL.

Now I'm trying to make search-sidebar.php display 2 separate input fields just like in the DATEINTERVAL case but of number type with step = 1.

[marius-ciclistu]

So
4
oc-includes/osclass/frm/Field.form.class.php

following the above mods I managed to decide about the code :

EDITED again to resolve this issue also:https://forums.osclass.org/development/why-aren't-the-dateinterval-params-shown-in-search-sidebar-is-they-are-in-url/msg159193/#msg159193 .

I put:

Code: [Select]

        static public function meta($field = null, $search = false) {

            if($field!=null) {
                // date interval
                if($field['e_type']=='DATEINTERVAL') { $type='DATEINTERVAL';
                   /* $field['s_value'] = array();
                    $field['s_value']['from']   = '';
                    $field['s_value']['to']     = '';*/

                    if(!$search) {
                        $aInterval = Field::newInstance()->getDateIntervalByPrimaryKey($field['fk_i_item_id'], $field['pk_i_id']);

                        if(is_array($aInterval) && !empty($aInterval) ) {
                            $temp['from']       = @$aInterval['from'];
                            $temp['to']         = @$aInterval['to'];
                            $field['s_value']   = $temp;
                        }
                    } /*else {
                        $temp['from']   = Params::getParam('meta['.$field['pk_i_id'].'][from]');
                        $temp['to']     = Params::getParam('meta['.$field['pk_i_id'].'][to]');
                        $field['s_value'] = $temp;
                    }*/
                }
                // end date interval

                if(Session::newInstance()->_getForm('meta_'.$field['pk_i_id']) != ""){
                    $field['s_value'] = Session::newInstance()->_getForm('meta_'.$field['pk_i_id']);
                } else if(!isset($field['s_value']) || $field['s_value']=='') {
                    $s_value = Params::getParam('meta');
                    $field['s_value'] = '';
                    if(isset($s_value[$field['pk_i_id']])) {
                        if($search) {
                            if($field['e_type']=='NUMERIC') {
                               $tmp['from'] = $s_value[$field['pk_i_id']]['from'];
                               $tmp['to'] = $s_value[$field['pk_i_id']]['to'];
                               $field['s_value'] = $tmp;
                            } else {
                                $field['s_value'] = $s_value[$field['pk_i_id']];
                            }
                        } else {
                            $field['s_value'] = $s_value[$field['pk_i_id']];   
                           
                        }
                    }
                }instead of

Code: [Select]

        static public function meta($field = null, $search = false) {

            if($field!=null) {
                // date interval
                if($field['e_type']=='DATEINTERVAL') {
                    $field['s_value'] = array();
                    $field['s_value']['from']   = '';
                    $field['s_value']['to']     = '';

                    if(!$search) {
                        $aInterval = Field::newInstance()->getDateIntervalByPrimaryKey($field['fk_i_item_id'], $field['pk_i_id']);

                        if(is_array($aInterval) && !empty($aInterval) ) {
                            $temp['from']       = @$aInterval['from'];
                            $temp['to']         = @$aInterval['to'];
                            $field['s_value']   = $temp;
                        }
                    } else {
                        $temp['from']   = Params::getParam('meta['.$field['pk_i_id'].'][from]');
                        $temp['to']     = Params::getParam('meta['.$field['pk_i_id'].'][to]');
                        $field['s_value'] = $temp;
                    }
                }
                // end date interval
                if(Session::newInstance()->_getForm('meta_'.$field['pk_i_id']) != ""){
                    $field['s_value'] = Session::newInstance()->_getForm('meta_'.$field['pk_i_id']);
                } else if(!isset($field['s_value']) || $field['s_value']=='') {
                    $s_value = Params::getParam('meta');
                    $field['s_value'] = '';
                    if(isset($s_value[$field['pk_i_id']])) {
                        $field['s_value'] = $s_value[$field['pk_i_id']];
                    }
                }


and new mod

EDITED
Reason
static public function meta($field = null, $search = false) {
is used to display metas not only in search-sidebar but also in admin when adding/editing an item and in item-add.php as well.
EDITED for search-sidebar values to work.

Code: [Select]

                } else {
                    if($field['e_type']=="NUMERIC") {
                        if($search) {
                            echo '<h6>'.$field['s_name'].'</h6>';
                            echo '<input type="text" id="meta_'.$field['s_slug'].'_from" name="meta['.$field['pk_i_id'].'][from]" value="'.$field['s_value']['from'].'" />';
                            echo ' - <input type="text" id="meta_'.$field['s_slug'].'_to" name="meta['.$field['pk_i_id'].'][to]" value="'.$field['s_value']['to'].'" />';
                        } else {
                            echo '<label for="meta_'.$field['s_slug'].'">'.$field['s_name'].': </label>';
                            echo '<input id="meta_'.$field['s_slug'].'" type="number" step="1" name="meta['.$field['pk_i_id'].']" value="' . osc_esc_html((isset($field) && isset($field["s_value"])) ? $field["s_value"] : "") . '" />';
                        }

                    } else {
                            if($search) {
                                echo '<h6>'.$field['s_name'].'</h6>';
                            } else {
                                echo '<label for="meta_'.$field['s_slug'].'">'.$field['s_name'].': </label>';
                            }
                            echo '<input id="meta_'.$field['s_slug'].'" type="text" name="meta['.$field['pk_i_id'].']" value="' . osc_esc_html((isset($field) && isset($field["s_value"])) ? $field["s_value"] : "") . '" />';
                    }
                }
            }
        }
instead of

Code: [Select]

                } else {
                    if($search) {
                        echo '<h6>'.$field['s_name'].'</h6>';
                    } else {
                        echo '<label for="meta_'.$field['s_slug'].'">'.$field['s_name'].': </label>';
                    }
                    echo '<input id="meta_'.$field['s_slug'].'" type="text" name="meta['.$field['pk_i_id'].']" value="' . osc_esc_html((isset($field) && isset($field["s_value"])) ? $field["s_value"] : "") . '" />';
                }
            }
        }

[marius-ciclistu]

Removed - Thinking out loud.

[marius-ciclistu]

Now I'm stuck.
All workes ok  except the filtering.
The numeric value is shown in item.php, item-post.php, item-edit.php and in admin area for item post/add.
The 2 input fields for interval are shown in search-sidebar.php.

PS.
Regarding the below picture, I opened a new question here: https://forums.osclass.org/development/why-aren't-the-dateinterval-params-shown-in-search-sidebar-is-they-are-in-url/msg159193/#msg159193

[marius-ciclistu]

EDIT again
I tested by entering manually the url like this:

/search/sOrder,dt_pub_date/iOrderType,desc/meta47-from,2017/meta47-to,2020/category,67

and for an item with 2018 in meta67.s_value it workes. So why after filtering the from and to values aren't working...?

[marius-ciclistu]

Removed. Thinking out loud.