When database return null - {{other}} instead of columns names

Dimitry commented on Mar 19, 2019

Dear support, good day.

When database return null - {{other}} instead of columns names like on the picture.

How can i fix this?

David Winterburn commented on Mar 20, 2019

Hi Dimitry,

Would you please post your sample data and your php code? Thanks!

Dimitry commented on Mar 20, 2019

Hi David.

Here is my php code:

protected function defaultParamValues()
    {
        return array(
            "dateRange" => array(
                date("Y-m-d H:i:s", strtotime('- 1 month')),
                date("Y-m-d H:i:s", strtotime('+ 1 day')),
            ),
            "clients" => array(),
            "offers" => array(),
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "dateRange" => "dateRange",
            "clients" => "clients",
            "offers" => "offers",
        );
    }

    function setup()
    {
        $params = array(
            ":startDate" => $this->params["dateRange"][0],
            ":endDate" => $this->params["dateRange"][1],
        );

        if($this->params["clients"]!=array())
        {
            $params[":clients"] = $this->params["clients"];
        }

        if($this->params["offers"]!=array())
        {
            $params[":offers"] = $this->params["offers"];
        }

        $sql = $this->buildSql();

        $this->src('pgsql')
            ->query($sql)
            ->params($params)
            ->pipe(new Pivot(array(
                "dimensions" => array(
                    "column" => "",
                    "row" => "client, offer, name",
                ),
                "aggregates" => array(
                    "sum" => "init_count_balance, count_income, count_outcome, final_count_balance, init_summ_balance, summ_income, summ_outcome, final_summ_balance"
                ),
            )))
            ->pipe($this->dataStore('deptsoffers'));

        $this->src('pgsql')
            ->query('SELECT id, name FROM clients')
            ->pipe($this->dataStore('clients'));
        $this->src('pgsql')
            ->query('SELECT id, name FROM offers')
            ->pipe($this->dataStore('offers'));

Here is sample data:

but in this case everything working fine:

but when database return NULL i am having {{other}}

David Winterburn commented on Mar 20, 2019

Hi Dimitry,

By default, if a row is processed by Pivot ($row) with certain defined fields and the row doesn't contain a field (for example: "client"), Pivot will assign value "{{other}}" for that $row["client"]. In the end the "client" field will include distinct values like "Company A", "Company B",... and "{{other}}" for all the null $row["client"].

If you want to avoid the case when all values are "{{other}}", it's best to filter out totally null rows with either Filter or Map process so that no null row is processed by Pivot. Let us know if you need any help with filtering null rows. Thanks!

Dimitry commented on Mar 20, 2019

Hi David,

Thanks for reply!

Can you give me simple example or link where i can see how to filter null rows.

Thanks!

David Winterburn commented on Mar 20, 2019

Hi Dimitry,

To filter out totally null rows, please try the following code:

        ->pipe(new \koolreport\processes\Map([
            '{value}' => function ($row) {
                $isAllNull = true;
                foreach ($row as $v)
                    if (isset($v))
                        $isAllNull = false;
                if (! $isAllNull)
                    return $row;
            },
        ]))

Dimitry commented on Mar 20, 2019

Hi David,

This works only if any data passed, if database return null, this block of code not fires.

David Winterburn commented on Mar 20, 2019

Hi Dimitry,

Please send us an email to support@koolphp.net. We will send you a development version of the Pivot package that should address this issue. Thanks!

KoolReport's Forum

When database return null - {{other}} instead of columns names #753

Build Your Excellent Data Report