CODEWITHSUNDEEP
node.jspostgresqlknex.js
Sérgio Avilla
Sérgio Avilla

Reputation: 27

How can I do this in Knex without two queries

I want to do this search without use two queries:

app.db('users').where({
            id: req.userData.id
        }).first().then(result => {

            if (!result) {
                throw new CompleteError('Usuário não encontrado.', 404)
            }

            app.db('addresses').where({
                id: result.address_id
            }).first().then(address_result => {
                if (address_result) {
                    result.address = address_result
                    res.json(result)
                } else {
                    throw new CompleteError('Falha ao buscar endereço do usuário.', 500)
                }
            }).catch(e => {
                return next(e)
            })

        }).catch(e => {
            return next(e)
        })

If one of the searchs fail or dont find any register, the search progress stop and throw an error. The database is postgresql. If there isnt any alternative to this method, please help how to improve this code, I am a starter developer, I need your opnion. Please, sorry my english.

The result must to be like this:

{
  "id": 21,
  "name": "Sérgio",
  "cpf": "11111111111",
  "email": "[email protected]",
  "pass": "$2a$10$Jx7wAd3QSMFlkXi3g0eVOOoW8o65OgDZVQ7wTWuxquytVhUhrUf/.",
  "phone": "5544313113131",
  "last_access": "2020-08-11T10:14:20.207Z",
  "created_at": "2020-08-11T10:14:20.207Z",
  "deleted_at": null,
  "address_id": 21,
  "address": {
    "id": 21,
    "street": "Street Manu",
    "number": "851",
    "complement": "A",
    "city": "Mari Hill",
    "state": "Para",
    "country": "Brazil",
    "last_access": "2020-08-11T10:14:19.837Z",
    "created_at": "2020-08-11T10:14:19.837Z",
    "deleted_at": null
  }
}

Upvotes: 0

Views: 152

Answers (1)

Kryten
Kryten

Reputation: 15760

You can execute this query with a JOIN and then some re-arranging of the results.

The query you're looking for looks something like this:

SELECT *
FROM users
    LEFT JOIN addresses ON addresses.id=users.address_id;

(I'm more familiar with MySQL/MariaDB so the syntax may be a little different).

The LEFT JOIN means that the address columns will be null if the user is found but no address is found.

This can be translated to the following Knex expression:

app.db('users')
    .leftJoin('addresses', 'addresses.id', '=', 'users.address_id')
    .select(
        'users.id',
        'users.name',
        'users.cpf',
        'users.email',
        'users.pass',
        'users.phone',
        'users.last_access',
        'users.created_at',
        'users.deleted_at',
        'users.address_id',
        'addresses.id AS addr_id',
        'addresses.street AS addr_street',
        'addresses.number AS addr_number',
        'addresses.complement AS addr_complement',
        'addresses.city AS addr_city',
        'addresses.state AS addr_state',
        'addresses.country AS addr_country',
        'addresses.last_access AS addr_last_access',
        'addresses.created_at AS addr_created_at',
        'addresses.deleted_at AS addr_deleted_at'
    )
    .first()
    .then((result) => {
        // no result at all? no user found
        if (!result) {
            throw new CompleteError('Usuário não encontrado.', 404);
        }

        // no addr_id? user found but not the address
        if (!result.addr_id) {
            throw new CompleteError('Falha ao buscar endereço do usuário.', 500);
        }

        // result contains all the fields from BOTH tables as a flat object
        // you'll need to rearrange things
        return {
            // assign properties to new names...
            id: result.id,
            // and so on...
        };
    });

Notice that I've renamed all the columns from addresses in the select() call. This is because Knex doesn't assign new column names in a query with a JOIN clause and you will wind up with duplicate column names (and a lot of confusion) unless you explicitly name the columns.

Upvotes: 1

Related Questions