SELECT QUERY With specific fields.

[AndrianD]

Hello, I was wondering how to make a SELECT query with specific fields.

I mean how can I do something like this: SELECT field1, field2 FROM table;

Thank you for your answers!

[oscarotero]

$db->table->select() always select all fields, because rows need to have all fields. But you can execute manual queries, like $db->execute('SELECT field1, field2 FROM table')

[AndrianD]

You cannot do something like $db->table->select()->field(['field1', 'field2']); ?

[oscarotero]

That is not allowed for now.

[AndrianD]

you plan to put it later or not ? I've got table with many rows and I want to select only 5 rows of them, why should I perform 2 select request than one ?

[oscarotero]

I guess when you say rows, you mean columns (otherwise it can achieve with limit()).

The reason is when you select a post, you get a Row instance representing that post. If you select again the same post, instead performing a new select, you get the previous Row instance, so the changes saved in one side will affect to the other side.

If you select a post limiting the amount of columns, you wont get the remaining columns in the future, because the same row will be used always. If you never need these columns, a workaround is modify the database scheme to hidden these columns in simplecrud. For example:

$scheme = $db->getScheme();

unset($scheme['table']['fields']['column1']);
unset($scheme['table']['fields']['column2']);

$db->setScheme($scheme);

Now, column1 and column2 in the table table will not be visible to simplecrud, so they wont be selected. But note that this will affects to all queries.

[AndrianD]

it would be better to have columns directly like you do whith update/insert method like you did here:

//Update a post
$db->post[3] = [
    'title' => 'Hello world'
];

//Insert a new post
$db->post[] = [
    'title' => 'Hello world 2'
];

and a select (that is missing)

$db->post[1]->select()->field(['title', 'id', 'name']);

to display only the needed fields.

Your method above is not the best approach if you deal with a row that have multiples columns. One line and that'all we need.

Make that magical please ;)

[darkvovich]

+1 This example not working TOO: $db->clients->select()->columns('name')->columns('id AS id_str')->where('id = ', 2)->orderBy('id DESC')->limit(15)->get(); But Atlas PDO have this

[oscarotero]

I'm not sure if this can be implemented easily in Simple Crud. As explained above, this can generate future bugs. For example:

$client = $db->clients->select()->columns('name')->where('id = ', 2)->get();

$client = $db->clients->select()->where('id = ', 2)->get();

SimpleCrud always save the rows in a internal cache, so the next time that you select the client with id 2, the cached row is returned. In this example, the second select will return the same result cached in the first select, that contains only the name column. This is why it need to select all columns.

[darkvovich]

I'm not sure if this can be implemented easily in Simple Crud. As explained above, this can generate future bugs. For example:

Ok, And what about LEFT JOIN?

1. How to get All fields after: $db->leads->select()->join('LEFT', 'clients', 'clients.id = leads.clientId')
2. How to get fields that have same names (clients.id AS cid)?

[oscarotero]

This should work:

// Select the first table
$leads = $db->leads->select()->get();

// Load the clients of the selected rows
$leads->clients;

// Now you can iterate:
foreach ($leads as $lead) {
    foreach ($lead->clients as $client) {
        var_dump($client);
    }
}

The columns method is used to get additional values, different from the regular fields. For example:

$leads = $db->leads->select()->column("(field1 + field2) as result")->get();

foreach ($leads as $lead) {
    var_dump($lead->result);
}