0

I want to get column of operated value with multi columns from MySQL in CI4 project.

Here is what I tried.

There are 3 fields qty, size, unit_price in table products and I want to get multiplied value of all of these 3 fields.

class ProductModel extends Model
{
    protected $allowedFields = ['size', 'qty', 'unit_price'];

    public function getPriceOfOneProduct()
    {
        return $this
            ->select('qty * size * unit_price AS price_of_one_product')
            ->findAll();
    }
}

As you can see I'm trying to get a column like this: qty * size * unit_price

But it's not indicated in $allowedFields so CI4 returns error like this:

`Unknown column 'qty * size * unit_price' in 'field list' `

Do I must use query builder or is there any solution for this issue? Please kindly answer my question if you know how to handle this. Thank you.

Sweet Carrot
  • 1
  • 1
  • 4
  • it looks like one of the fields you are using can not be found... can you show your tables structure? use `desc table_name` in mysql and then post in your question. – Jorge Campos Mar 07 '23 at 07:21
  • Thanks for your reply. Jorge I updated my question, and it will be easier to understand. Let's say there are 3 fields and I want to get multiplied value of three columns. – Sweet Carrot Mar 07 '23 at 07:29
  • try adding parenthesis around the fields `... (qty * size * unit_price) as ...` either that or that and separate the `as` as a different parameter like this: `select('(qty * size * unit_price)', 'price_of_one_product')` been a few years without coding in php... – Jorge Campos Mar 07 '23 at 07:51

1 Answers1

0

Do like this

class ProductModel extends Model
{
    protected $table = 'products';

    public function getPriceOfOneProduct()
    {
        $query = $this->db->query('SELECT (qty * size * unit_price) AS price_of_one_product FROM products');
        return $query->getResult();
    }
}
Abdulla Nilam
  • 36,589
  • 17
  • 64
  • 85
  • Thank you, Abdulla. Of course, If I use query builder, I can do it. But I don't want to use it. Want to get data with Entity class. – Sweet Carrot Mar 08 '23 at 07:04