Mysql CONCAT with peewee

Question

I am using peewee for interactions with my database.
I have for following schema:

CREATE TABLE `chat` (
  `id` int(11) NOT NULL,
  `user_a` int(11) NOT NULL,
  `user_b` int(11) NOT NULL,
  `hash` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `chat` (`id`, `user_a`, `user_b`, `hash`) VALUES
(1, 1, 2, '1_2'),
(2, 6, 1, '1_6');

-- --------------------------------------------------------

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`) VALUES
(1, 'John'),
(2, 'William'),
(3, 'Mike'),
(4, 'Liam'),
(5, 'Noah'),
(6, 'Mason'),
(7, 'Ethan'),
(8, 'Ava');

And I am trying to build a query like this (I need select a man to begin a new chat with. Is there a better solution for this problem?):

SELECT id, name FROM user WHERE CONCAT(id, "_", 2) NOT IN (SELECT hash FROM chat) AND CONCAT(2, "_", id) NOT IN (SELECT hash FROM chat)

But I don't know how to create such query with peewee. Here's peewee Model:

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    name = CharField()

class Chat(BaseModel):
    user_a = ForeignKeyField(User, backref='chats_inviter')
    user_b = ForeignKeyField(User, backref='chats_invited')
    hash = CharField(unique=True)

The thing I got wrong while creating the query is a concat operation (peewee makes "||" operator and trying to convert "_" separator to integer instead of using the CONCAT keyword).

Is there anything I can do to make peewee make a right "concat" clause?

Answer 1

Your example:

SELECT id, name FROM user WHERE CONCAT(id, "_", 2) NOT IN (SELECT hash FROM chat) AND CONCAT(2, "_", id) NOT IN (SELECT hash FROM chat)

You might write:

q = (User
     .select()
     .where(
        fn.CONCAT(User.id, '_', 2).not_in(Chat.select(Chat.hash)),
        fn.CONCAT(2, '_', User.id).not_in(Chat.select(Chat.hash))))

You can express any sql function using Peewee's magic "fn" helper:

fn.CONCAT(User.id, '_', 2)  # Will generate the appropriate SQL.