How to sort an EAV model that have null values?

We have an EAV (Entity-Attribute-Value) data model that can be simplified with those 3 tables:

We want to sort entities on certain field:

SELECT entities.*
FROM entities
  JOIN values ON values.entity_id = entities.entity_id
  JOIN fields ON fields.field_id = values.field_id
WHERE fields.name = 'my-field'
ORDER BY values.value DESC

However, this will not include entities that do not have values for this field. The wanted behavior would be to have those NULL entries at the beginning of the result, like usual ORDER BY.

Do you have suggestion of how to include those values ? Thanks

EDIT: Here is a concrete example:

Imagine we want to display and sort a list of 3 users, which are 3 entities having 2 fields, name and age:

The Entity table:

entity_id 
---------
       1         
       2       
       3       

The Field table:

field_id | name
---------|-------------
       1 | "firstname"
       2 | "age"

The Value table:

entity_id | field_id |     value
----------|----------|-----------
        1 |        1 |   "Alice"
        2 |        1 |     "Bob"
        3 |        1 | "Charlie"
        1 |        2 |      "24"
        2 |        2 |      "27"

Here, Charlie have no value for age. How to sort users by the age field without removing Charlie ?

The above query will remove it from the result-set.

Answer

Note: I’m still somewhat confused about what you want your resultset to look like.

select *
  from value v1
  left join value v2 on (v1.entity_id = v2.entity_id 
                         and v2.field_id = 2)
 where v1.field_id = 1 -- assuming all users have a name
 order by v2.value;

Result:

ENTITY_ID | FIELD_ID |     VALUE |  ENTITY_ID | FIELD_ID | VALUE
----------|----------|-----------|------------------------------
        1 |        1 |   "Alice" |          1 |        2 | "24"
        2 |        1 |     "Bob" |          2 |        2 | "27"
        3 |        1 | "Charlie" |       null |     null | null

---

Or if you prefer the names of the fields:

select v1.entity_id, f1.name, v1.value, f2.name, v2.value
  from value v1
  join field f1 on v1.field_id = f1.field_id
  left join value v2 on (v1.entity_id = v2.entity_id 
                         and v2.field_id = 2)
  left join field f2 on v2.field_id = f2.field_id                         
 where f1.name = 'firstname' -- assuming all users have a name
 order by v2.value;

Result:

ENTITY_ID | NAME        |     VALUE |  NAME | VALUE
----------|-------------|-----------|--------------
        1 | "firstname" |   "Alice" | "age" |  "24"
        2 | "firstname" |     "Bob" | "age" |  "27"
        3 | "firstname" | "Charlie" |  null |  null

---

For the case where there’s an entity, that doesn’t have a firstname, I added two rows to entity and a row to value:

insert into entity values (4);
insert into entity values (5);
insert into value values (4, 2, '102');

If a record exists in the entity table, it will always be in this result:

select *
  from entity e
  left join value v1 on (e.entity_id = v1.entity_id
                         and v1.field_id = 1) -- firstname
  left join value v2 on (e.entity_id = v2.entity_id 
                         and v2.field_id = 2) -- age
-- order by v2.value IS NULL; -- age nulls last

Result:

ENTITY_ID | ENTITY_ID |   FIELD_ID |     VALUE |  ENTITY_ID | FIELD_ID | VALUE
----------|-----------|------------|-----------|------------|----------|------
        1 |         1 |          1 |   "Alice" |          1 |        2 |  "24"
        2 |         2 |          1 |     "Bob" |          2 |        2 |  "27"
        4 |      null |       null |      null |          4 |        2 | "102"
        3 |         3 |          1 | "Charlie" |       null |     null |  null
        5 |      null |       null |      null |       null |     null |  null