Advertisement:

Author Topic: SOLVED!. New type of custom field: number input and interval filter  (Read 2084 times)

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
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.
« Last Edit: January 03, 2018, 01:36:22 pm by marius-ciclistu »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #1 on: January 02, 2018, 12:31:00 am »
Should I modify the existing text custom field or should I add a new one ?

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #2 on: January 02, 2018, 02:11:09 pm »
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.
« Last Edit: January 02, 2018, 02:15:05 pm by marius-ciclistu »

teseo

  • Hero Member
  • *****
  • Posts: 6169
Re: New type of custom field suggestion: number input and interval filter
« Reply #3 on: January 02, 2018, 02:26:38 pm »
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

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #4 on: January 02, 2018, 02:35:03 pm »
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.")"; ?

« Last Edit: January 02, 2018, 03:09:54 pm by marius-ciclistu »

teseo

  • Hero Member
  • *****
  • Posts: 6169
Re: New type of custom field suggestion: number input and interval filter
« Reply #5 on: January 02, 2018, 02:51:02 pm »
Quote
$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)
« Last Edit: January 02, 2018, 02:56:20 pm by teseo »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #6 on: January 02, 2018, 03:19:18 pm »
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.
« Last Edit: January 03, 2018, 03:38:18 pm by marius-ciclistu »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #7 on: January 02, 2018, 03:23:21 pm »
Removed as without importance
« Last Edit: January 02, 2018, 05:16:37 pm by marius-ciclistu »

teseo

  • Hero Member
  • *****
  • Posts: 6169
Re: New type of custom field suggestion: number input and interval filter
« Reply #8 on: January 02, 2018, 03:32:36 pm »
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

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #9 on: January 02, 2018, 03:39:06 pm »
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

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #10 on: January 02, 2018, 04:04:06 pm »
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"] : "") . '" />';
                }
            }
        }
« Last Edit: January 03, 2018, 01:39:28 pm by marius-ciclistu »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #11 on: January 02, 2018, 04:56:47 pm »
Removed - Thinking out loud.
« Last Edit: January 02, 2018, 09:26:08 pm by marius-ciclistu »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #12 on: January 02, 2018, 06:36:41 pm »
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
« Last Edit: January 02, 2018, 07:14:26 pm by marius-ciclistu »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #13 on: January 02, 2018, 07:58:36 pm »
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...?
« Last Edit: January 02, 2018, 10:08:18 pm by marius-ciclistu »

marius-ciclistu

  • issues
  • Hero Member
  • *
  • Posts: 1623
  • "BE GRATEFUL TO THOSE THAT SUPPORTED YOU"
Re: New type of custom field suggestion: number input and interval filter
« Reply #14 on: January 02, 2018, 10:18:35 pm »
Removed. Thinking out loud.
« Last Edit: January 03, 2018, 12:11:23 pm by marius-ciclistu »