SQL - Django : en rawSQL obtenir la limite de lignes et de date.year - Stack Overflow

I want to fetch the items with outDate in year 'year', and only fetch the top 'num' rows.

Here are my code

            year = form.cleaned_data['year']
            num = form.cleaned_data['limit']

            cursor = connection.cursor()
            cursor.execute("SELECT br.callNumber_id, bk.title, COUNT(br.callNumber_id) as borrows FROM books_borrowing, books_book bk WHERE bk.id = br.callNumber_id br GROUP BY br.callNumber_id ORDER BY borrows DESC" )
            books = cursor.fetchall()

I tried to add LIMIT num and year(br.outDate) = year but I received error saying my syntax are not correct. I'm using mySQL with Django.

What should I do to implement my SQL?

Your query syntax doesn't look quite right.. You need to alias books_borrowing with br correctly.

Try the following:

cursor.execute("""
    SELECT br.callNumber_id,
           bk.title,
           COUNT(br.callNumber_id) AS borrows
    FROM books_borrowing br,
         books_book bk
    WHERE bk.id = br.callNumber_id
    GROUP BY br.callNumber_id
    ORDER BY borrows DESC
    """)

I want to fetch the items with outDate in year 'year', and only fetch the top 'num' rows.

Here are my code

            year = form.cleaned_data['year']
            num = form.cleaned_data['limit']

            cursor = connection.cursor()
            cursor.execute("SELECT br.callNumber_id, bk.title, COUNT(br.callNumber_id) as borrows FROM books_borrowing, books_book bk WHERE bk.id = br.callNumber_id br GROUP BY br.callNumber_id ORDER BY borrows DESC" )
            books = cursor.fetchall()

I tried to add LIMIT num and year(br.outDate) = year but I received error saying my syntax are not correct. I'm using mySQL with Django.

What should I do to implement my SQL?

Your query syntax doesn't look quite right.. You need to alias books_borrowing with br correctly.

Try the following:

cursor.execute("""
    SELECT br.callNumber_id,
           bk.title,
           COUNT(br.callNumber_id) AS borrows
    FROM books_borrowing br,
         books_book bk
    WHERE bk.id = br.callNumber_id
    GROUP BY br.callNumber_id
    ORDER BY borrows DESC
    """)

Source