mysql how to make a field equal to quantity of certain user's rows in the current table?

qco9c6ql 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(96)

I'm developing a telegram bot. So i have three tables in my database:

Users - stores information about users. fields:
user: stores user's telegram id. it needs to assign a record to the user
first name, last name, etc
Notes - stores user's notes. fields:
user: foreign key related to field user in table Users.
id: id of record
text of note, date created, etc
Records - stores user's reminders. it is similar to the table Notes. fields:
user: foreign key related to field user in table Users.
id: id of record
text of record, date, etc
the thing is when you ask the bot to show your records (from Notes or Records) it sends you a message with the text: "send me /del{id} to delete this record", where id is record's id. Here is the problem: if there are 50 rows in the table and somebody started to use my bot, when he creates the 1st record then bot sends him a message "send me /del51 to delete this record".
so here is my question: is there any possibility to make record's id equal to quantity of user's records in the current table?
I need someting like that:
| user | text | created | id |
| ------------ | ------------ | ------------ | ------------ |
| 1234 | qwerty | 15.12.2022 | 1 |
| 1234 | qwerty1 | 17.12.2022 | 2 |
| 1234 | qwerty2 | 18.12.2022 | 3 |
| 1234 | qwerty3 | 18.12.2022 | 4 |
| 456 | xyzr1 | 16.12.2022 | 1 |
| 456 | xyzr2 | 17.12.2022 | 2 |
| 456 | xyzr3 | 18.12.2022 | 3 |
| 456 | xyzr4 | 19.12.2022 | 4 |

来源：https://stackoverflow.com/questions/74837429/how-to-make-a-field-equal-to-quantity-of-certain-users-rows-in-the-current-tabl

1条答案

按热度按时间

bkhjykvo1#

my solution is:

count how much has current user rows in the table
add one so this is id

def get_records(table: str, user_id: int) -> list:
    cursor.execute(f'SELECT * FROM {table} WHERE user = {user_id}')
    records = cursor.fetchall()
    return records

def generate_id(table: str, user_id: int) -> int:
    records = get_records(table, user_id)
    return len(records) + 1

赞(0）回复(0）举报 2022-12-22

我来回答

mysql how to make a field equal to quantity of certain user's rows in the current table?

1条答案

相关问题

热门标签

最新问答