Search for rating for first or second ever review

Can this feature be introduced where we can search cards by their first ever rating or second and so on rating?

https://docs.ankiweb.net/searching.html#first-answered

This would give cards that were done for the first time. What i wanted to do was search for cards that had their first ever rating as good or again no matter when they were first introduced. The reason why i want to do this is that i have observed that when i do my cards around 30 percent of them are significantly difficult for me as opposed to the other 70 percent and i usually hit again twice or more on these cards. Now with the introduction of fsrs i want to move such cards with their first two ratings as again to a different deck so that i can have a different preset and thereby parameters for them.

As far as I know, it is impossible to do such a search with Anki’s built-in search function.

Incidentally, there are cases where you can’t find the notes or cards you are looking for with the built-in search function, but you can do so with an SQL query. (See Complex searches? SQL!.) For example, the following SQL query will retrieve the IDs of cards rated “Again” on the first or second review:

SELECT
    cid
FROM
    revlog AS r1
WHERE
    ease = 1
    AND id IN (
        SELECT
            id
        FROM
            revlog AS r2
        WHERE
            r1.cid = r2.cid
        ORDER BY
            id
        LIMIT
            2
    )

And, if I were to do what you want to do, I would probably do the following:

  1. Open the Anki’s debug console
  2. Enter the following code in the upper field:
    from aqt.utils import showText
    
    sql = """
    SELECT
        cid
    FROM
        revlog AS r1
    WHERE
        ease = 1
        AND id IN (
            SELECT
                id
            FROM
                revlog AS r2
            WHERE
                r1.cid = r2.cid
            ORDER BY
                id
            LIMIT
                2
        )
    """
    cids = (str(cid) for cid in mw.col.db.list(sql))
    search_query = f'"cid:{",".join(cids)}"'
    showText(search_query, copyBtn=True)
    
  3. Press Ctrl + Enter to execute it
  4. Press the Copy to clipboard button on the dialog that pops up, then close that dialog
  5. Open the browser, set it to Cards mode (if it is in Notes mode), paste the text you just copied into the search field, and then run the search
  6. EditSelect All
  7. CardsChange Deck → Select another deck → Move Cards

Screenshot:

1 Like

I ran the script but it gives notes with first rating as again OR second rating as again. I want cards with first AND second rating as again.
Also can you tell me how to add for a third rating as well in this script just so i can compare the number of cards with two agains?
Thanks for the help btw.

Also i would appreciate it if @dae could have this feature built into anki

An SQL query for cards rated “Again” on both the first and second review:

WITH
    first_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
            )
    ),
    second_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
                OFFSET
                    1
            )
    )
SELECT
    first_review.cid
FROM
    first_review
    INNER JOIN second_review ON first_review.cid = second_review.cid

An SQL query for cards rated “Again” on the first, second, and third review:

WITH
    first_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
            )
    ),
    second_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
                OFFSET
                    1
            )
    ),
    third_review AS (
        SELECT
            cid,
            ease
        FROM
            revlog AS r1
        WHERE
            ease = 1
            AND id IN (
                SELECT
                    id
                FROM
                    revlog AS r2
                WHERE
                    r1.cid = r2.cid
                ORDER BY
                    id
                LIMIT
                    1
                OFFSET
                    2
            )
    )
SELECT
    first_review.cid
FROM
    first_review
    INNER JOIN second_review ON first_review.cid = second_review.cid
    INNER JOIN third_review ON first_review.cid = third_review.cid

( I am not very familiar with SQL so I can’t tell you, but surely there must be more concise and efficient SQL queries to get the same results as these. )

1 Like

I will keep an eye on demand for such a feature.

I am trying to do a related search for cards that were answered hard, good, or easy (or simply not again if that’s easier) on the 3 most recent attempts. Is it possible to do this using a similar SQL syntax? I haven’t done an SQL query but can try to follow the procedure outlined here.

I would also love to see this search functionality integrated into the native search, but an SQL workaround would be great in the meantime.

WITH
    last_rev AS (
        SELECT
            cid
        FROM
            revlog AS r1
        WHERE
            (ease BETWEEN 2 AND 4)
            AND (
                id IN (
                    SELECT
                        id
                    FROM
                        revlog AS r2
                    WHERE
                        r1.cid = r2.cid
                    ORDER BY
                        id DESC
                    LIMIT
                        1
                )
            )
    ),
    second_to_last AS (
        SELECT
            cid
        FROM
            revlog AS r1
        WHERE
            (ease BETWEEN 2 AND 4)
            AND (
                id IN (
                    SELECT
                        id
                    FROM
                        revlog AS r2
                    WHERE
                        r1.cid = r2.cid
                    ORDER BY
                        id DESC
                    LIMIT
                        1
                    OFFSET
                        1
                )
            )
    ),
    third_to_last AS (
        SELECT
            cid
        FROM
            revlog AS r1
        WHERE
            (ease BETWEEN 2 AND 4)
            AND (
                id IN (
                    SELECT
                        id
                    FROM
                        revlog AS r2
                    WHERE
                        r1.cid = r2.cid
                    ORDER BY
                        id DESC
                    LIMIT
                        1
                    OFFSET
                        2
                )
            )
    )
SELECT
    last_rev.cid
FROM
    last_rev
    INNER JOIN second_to_last ON last_rev.cid = second_to_last.cid
    INNER JOIN third_to_last ON last_rev.cid = third_to_last.cid

Thank for the reply. I tried doing this but I don’t think it’s working. I pasted it into the debut console but when I hit Ctrl + Enter, I am not getting a dialog box. When I paste the output into the ANKI search bar, it’s not working. Here is the output I am getting.

WITH
… last_rev AS (
… SELECT
… cid
… FROM
… revlog AS r1
… WHERE
… (ease BETWEEN 2 AND 4)
… AND (
… id IN (
… SELECT
… id
… FROM
… revlog AS r2
… WHERE
… r1.cid = r2.cid
… ORDER BY
… id DESC
… LIMIT
… 1
… )
… )
… ),
… second_to_last AS (
… SELECT
… cid
… FROM
… revlog AS r1
… WHERE
… (ease BETWEEN 2 AND 4)
… AND (
… id IN (
… SELECT
… id
… FROM
… revlog AS r2
… WHERE
… r1.cid = r2.cid
… ORDER BY
… id DESC
… LIMIT
… 1
… OFFSET
… 1
… )
… )
… ),
… third_to_last AS (
… SELECT
… cid
… FROM
… revlog AS r1
… WHERE
… (ease BETWEEN 2 AND 4)
… AND (
… id IN (
… SELECT
… id
… FROM
… revlog AS r2
… WHERE
… r1.cid = r2.cid
… ORDER BY
… id DESC
… LIMIT
… 1
… OFFSET
… 2
… )
… )
… )
… SELECT
… last_rev.cid
… FROM
… last_rev
… INNER JOIN second_to_last ON last_rev.cid = second_to_last.cid
… INNER JOIN third_to_last ON last_rev.cid = third_to_last.cid
Traceback (most recent call last):
File “aqt.debug_console”, line 294, in onDebugRet
File “”, line 2
last_rev AS (
IndentationError: unexpected indent