Cannot use Model first() and update() in table without primary key

[cobafesbuk]

Describe the bug
When using a table without primary key, it's not possible to use first() command.
I found the reason is 'ORDER BY <tablename>.<primary key>' is always appended to the end of the query.
Emptying the $primaryKey does not fix the problem.

Similiar problem with update(). It works fine with insert().

I'm using the query builder for workaround for now.

CodeIgniter 4 version
CodeIgniter 4.0.0 alpha.2

Context

• OS: Windows 10
• Web server XAMPP 3.2.2
• PHP version 7.2

[nowackipawel]

My PR#1325 or newer PR#1560 should solve your issue about update([col => 2, bar => [1,3]]) method.
You could pass an array of fields for your updates WHERE to get where col =2 and bar in(1,3)

The same with delete() method or PR#1441

[titounnes]

table without primary key is not good idea.

[nowackipawel]

I cannot agree with you. It depends what you are storing in it.

[lonnieezell]

@nowackipawel I'm curious what type of stuff you store that doesn't need a primary key? Doesn't that result in full table scans whenever you query?

[nowackipawel]

@lonnieezell I had in my mind that we could have complex primary keys and then it is important to specified it easy in the update query like: WHERE X = 1 and y = 4 and that is what PR#1325 or PR#1560 is doing well :).

However you ask about use case:.... I don't use primary keys in "storage-like" tables like login attempts when I am sure I won't need it sometimes I've got at max 3-4 tables like that in database which contains 60-70 tables.

[titounnes]

You can write like this.
$this->where(['key1'=>1,'key2'=>2])->update($array);

[titounnes]

Or like this.
$this->where(['key1'=>1,'key'=>2])->save($array);

[lonnieezell]

@nowackipawel I guess that makes sense. I've always still put a primary key in there because otherwise it will do a full table scan whenever you search and if those tables starting getting big, which a login attempts table likely will, that query is going to keep getting slower when I need to get there. Sounds like they are tables you don't access very much so likely not a pain point for you.

[lonnieezell]

This particular issue sounds like it's simple enough to fix, though, by checking to see if primary key is empty or not before adding the order by into the query.

[cobafesbuk]

Not directly related to the report (OUT OF TOPIC)

It seems that I encountered another issue because of not using primary key. It seems that the new CodeIgniter's design is built around primary key system?

I supposed to have '5' results from my query.
I want to count how many result for pagination so I used $query->countAllResults() to count it, the result was '2', I don't know where '2' came from.
I then tried to view the result using $query->getResult(), it shows '5' results correctly. $query->paginate() shows correct result as well.

Here's the test code I used

$query = $this->select('name, SUM(ctr_choose) as total_choose');
$query->join('food', 'user_answer.id_food=food.id');
$query->groupBy('id_food');
$compiled = $query->getCompiledSelect();
print_r($compiled);
echo '|';
$this->resetQuery();
$query = $this->select('name, SUM(ctr_choose) as total_choose');
$query->join('food', 'user_answer.id_food=food.id');
$query->groupBy('id_food');
print_r($query->countAllResults()); //result: 2 (incorrect)

echo '|';
$query = $this->query($compiled);
print_r(count($query->getResult())); //result: 5 (correct)
	
$link = mysqli_connect("localhost", "root", "", "battermod");
$result = mysqli_query($link, $compiled);
$num_rows = mysqli_num_rows($result);
echo "\n <$num_rows Rows>\n"; //result: 5 (correct)

Because there's no $this->db->last_query();, I don't know what kind of query countAllResults produced.

[cobafesbuk]

@titounnes
It doesn't work, the query always append this at the end of query. Same problem with first()

AND <tablename>.id IN (<id>)

[Farozy]

$this->model->set('name', $input['name']);
$this->model->where('id', $input['id']);
$this->model->update();