home / 24ways-fts4

24ways-fts4

Custom SQL query returning 5 rows

Query parameters

titleauthordecode_matchinfo(matchinfo(articles_fts, "pcx"))json_object("pre", annotate_matchinfo(matchinfo(articles_fts, "pcxnalyb"), "pcxnalyb"))
Geotag Everywhere with Fire Eagle Ben Ward [2, 3, 0, 2, 2, 0, 0, 0, 1, 163, 31, 0, 2, 2, 0, 0, 0, 2, 92, 21]
{
  "p": {
    "value": 2,
    "title": "Number of matchable phrases in the query",
    "idx": 0
  },
  "c": {
    "value": 3,
    "title": "Number of user defined columns in the FTS table",
    "idx": 1
  },
  "x": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          2,
          3,
          4
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          5,
          6,
          7
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_this_column_this_row": 1,
        "hits_this_column_all_rows": 163,
        "docs_with_hits": 31,
        "idxs": [
          8,
          9,
          10
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          11,
          12,
          13
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          14,
          15,
          16
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_this_column_this_row": 2,
        "hits_this_column_all_rows": 92,
        "docs_with_hits": 21,
        "idxs": [
          17,
          18,
          19
        ]
      }
    ],
    "title": "Details for each phrase/column combination"
  },
  "n": {
    "value": 336,
    "title": "Number of rows in the FTS4 table",
    "idx": 20
  },
  "a": {
    "title": "Average number of tokens in each column across the whole table",
    "value": [
      {
        "column_index": 0,
        "average_num_tokens": 5,
        "idx": 21
      },
      {
        "column_index": 1,
        "average_num_tokens": 2,
        "idx": 22
      },
      {
        "column_index": 2,
        "average_num_tokens": 1653,
        "idx": 23
      }
    ]
  },
  "l": {
    "title": "Number of tokens in each column of the current row of the FTS4 table",
    "value": [
      {
        "column_index": 0,
        "num_tokens": 5,
        "idx": 24
      },
      {
        "column_index": 1,
        "num_tokens": 2,
        "idx": 25
      },
      {
        "column_index": 2,
        "num_tokens": 2695,
        "idx": 26
      }
    ]
  },
  "y": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 27
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 28
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_for_phrase_in_col": 1,
        "idx": 29
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 30
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 31
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_for_phrase_in_col": 2,
        "idx": 32
      }
    ],
    "title": "Usable phrase matches for each phrase/column combination"
  },
  "b": {
    "title": "Bitfield showing which phrases occur in which columns",
    "value": [
      4,
      4
    ],
    "decoded": {
      "phrase_0": "00100000000000000000000000000000",
      "phrase_1": "00100000000000000000000000000000"
    }
  }
}
Unobtrusively Mapping Microformats with jQuery Simon Willison [2, 3, 1, 2, 2, 0, 0, 0, 39, 163, 31, 0, 2, 2, 0, 0, 0, 6, 92, 21]
{
  "p": {
    "value": 2,
    "title": "Number of matchable phrases in the query",
    "idx": 0
  },
  "c": {
    "value": 3,
    "title": "Number of user defined columns in the FTS table",
    "idx": 1
  },
  "x": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_this_column_this_row": 1,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          2,
          3,
          4
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          5,
          6,
          7
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_this_column_this_row": 39,
        "hits_this_column_all_rows": 163,
        "docs_with_hits": 31,
        "idxs": [
          8,
          9,
          10
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          11,
          12,
          13
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          14,
          15,
          16
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_this_column_this_row": 6,
        "hits_this_column_all_rows": 92,
        "docs_with_hits": 21,
        "idxs": [
          17,
          18,
          19
        ]
      }
    ],
    "title": "Details for each phrase/column combination"
  },
  "n": {
    "value": 336,
    "title": "Number of rows in the FTS4 table",
    "idx": 20
  },
  "a": {
    "title": "Average number of tokens in each column across the whole table",
    "value": [
      {
        "column_index": 0,
        "average_num_tokens": 5,
        "idx": 21
      },
      {
        "column_index": 1,
        "average_num_tokens": 2,
        "idx": 22
      },
      {
        "column_index": 2,
        "average_num_tokens": 1653,
        "idx": 23
      }
    ]
  },
  "l": {
    "title": "Number of tokens in each column of the current row of the FTS4 table",
    "value": [
      {
        "column_index": 0,
        "num_tokens": 5,
        "idx": 24
      },
      {
        "column_index": 1,
        "num_tokens": 2,
        "idx": 25
      },
      {
        "column_index": 2,
        "num_tokens": 2397,
        "idx": 26
      }
    ]
  },
  "y": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_for_phrase_in_col": 1,
        "idx": 27
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 28
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_for_phrase_in_col": 39,
        "idx": 29
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 30
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 31
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_for_phrase_in_col": 6,
        "idx": 32
      }
    ],
    "title": "Usable phrase matches for each phrase/column combination"
  },
  "b": {
    "title": "Bitfield showing which phrases occur in which columns",
    "value": [
      5,
      4
    ],
    "decoded": {
      "phrase_0": "10100000000000000000000000000000",
      "phrase_1": "00100000000000000000000000000000"
    }
  }
}
Front-End Code Reusability with CSS and JavaScript Trevor Morris [2, 3, 0, 2, 2, 0, 0, 0, 14, 163, 31, 0, 2, 2, 0, 0, 0, 1, 92, 21]
{
  "p": {
    "value": 2,
    "title": "Number of matchable phrases in the query",
    "idx": 0
  },
  "c": {
    "value": 3,
    "title": "Number of user defined columns in the FTS table",
    "idx": 1
  },
  "x": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          2,
          3,
          4
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          5,
          6,
          7
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_this_column_this_row": 14,
        "hits_this_column_all_rows": 163,
        "docs_with_hits": 31,
        "idxs": [
          8,
          9,
          10
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          11,
          12,
          13
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          14,
          15,
          16
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_this_column_this_row": 1,
        "hits_this_column_all_rows": 92,
        "docs_with_hits": 21,
        "idxs": [
          17,
          18,
          19
        ]
      }
    ],
    "title": "Details for each phrase/column combination"
  },
  "n": {
    "value": 336,
    "title": "Number of rows in the FTS4 table",
    "idx": 20
  },
  "a": {
    "title": "Average number of tokens in each column across the whole table",
    "value": [
      {
        "column_index": 0,
        "average_num_tokens": 5,
        "idx": 21
      },
      {
        "column_index": 1,
        "average_num_tokens": 2,
        "idx": 22
      },
      {
        "column_index": 2,
        "average_num_tokens": 1653,
        "idx": 23
      }
    ]
  },
  "l": {
    "title": "Number of tokens in each column of the current row of the FTS4 table",
    "value": [
      {
        "column_index": 0,
        "num_tokens": 8,
        "idx": 24
      },
      {
        "column_index": 1,
        "num_tokens": 2,
        "idx": 25
      },
      {
        "column_index": 2,
        "num_tokens": 1956,
        "idx": 26
      }
    ]
  },
  "y": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 27
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 28
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_for_phrase_in_col": 14,
        "idx": 29
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 30
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 31
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_for_phrase_in_col": 1,
        "idx": 32
      }
    ],
    "title": "Usable phrase matches for each phrase/column combination"
  },
  "b": {
    "title": "Bitfield showing which phrases occur in which columns",
    "value": [
      4,
      4
    ],
    "decoded": {
      "phrase_0": "00100000000000000000000000000000",
      "phrase_1": "00100000000000000000000000000000"
    }
  }
}
Finding Your Way with Static Maps Drew McLellan [2, 3, 0, 2, 2, 0, 0, 0, 1, 163, 31, 1, 2, 2, 0, 0, 0, 11, 92, 21]
{
  "p": {
    "value": 2,
    "title": "Number of matchable phrases in the query",
    "idx": 0
  },
  "c": {
    "value": 3,
    "title": "Number of user defined columns in the FTS table",
    "idx": 1
  },
  "x": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          2,
          3,
          4
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          5,
          6,
          7
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_this_column_this_row": 1,
        "hits_this_column_all_rows": 163,
        "docs_with_hits": 31,
        "idxs": [
          8,
          9,
          10
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_this_column_this_row": 1,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          11,
          12,
          13
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          14,
          15,
          16
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_this_column_this_row": 11,
        "hits_this_column_all_rows": 92,
        "docs_with_hits": 21,
        "idxs": [
          17,
          18,
          19
        ]
      }
    ],
    "title": "Details for each phrase/column combination"
  },
  "n": {
    "value": 336,
    "title": "Number of rows in the FTS4 table",
    "idx": 20
  },
  "a": {
    "title": "Average number of tokens in each column across the whole table",
    "value": [
      {
        "column_index": 0,
        "average_num_tokens": 5,
        "idx": 21
      },
      {
        "column_index": 1,
        "average_num_tokens": 2,
        "idx": 22
      },
      {
        "column_index": 2,
        "average_num_tokens": 1653,
        "idx": 23
      }
    ]
  },
  "l": {
    "title": "Number of tokens in each column of the current row of the FTS4 table",
    "value": [
      {
        "column_index": 0,
        "num_tokens": 6,
        "idx": 24
      },
      {
        "column_index": 1,
        "num_tokens": 2,
        "idx": 25
      },
      {
        "column_index": 2,
        "num_tokens": 1247,
        "idx": 26
      }
    ]
  },
  "y": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 27
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 28
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_for_phrase_in_col": 1,
        "idx": 29
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_for_phrase_in_col": 1,
        "idx": 30
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 31
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_for_phrase_in_col": 11,
        "idx": 32
      }
    ],
    "title": "Usable phrase matches for each phrase/column combination"
  },
  "b": {
    "title": "Bitfield showing which phrases occur in which columns",
    "value": [
      4,
      5
    ],
    "decoded": {
      "phrase_0": "00100000000000000000000000000000",
      "phrase_1": "10100000000000000000000000000000"
    }
  }
}
Your jQuery: Now With 67% Less Suck Scott Kosman [2, 3, 1, 2, 2, 0, 0, 0, 19, 163, 31, 0, 2, 2, 0, 0, 0, 2, 92, 21]
{
  "p": {
    "value": 2,
    "title": "Number of matchable phrases in the query",
    "idx": 0
  },
  "c": {
    "value": 3,
    "title": "Number of user defined columns in the FTS table",
    "idx": 1
  },
  "x": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_this_column_this_row": 1,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          2,
          3,
          4
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          5,
          6,
          7
        ]
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_this_column_this_row": 19,
        "hits_this_column_all_rows": 163,
        "docs_with_hits": 31,
        "idxs": [
          8,
          9,
          10
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 2,
        "docs_with_hits": 2,
        "idxs": [
          11,
          12,
          13
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_this_column_this_row": 0,
        "hits_this_column_all_rows": 0,
        "docs_with_hits": 0,
        "idxs": [
          14,
          15,
          16
        ]
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_this_column_this_row": 2,
        "hits_this_column_all_rows": 92,
        "docs_with_hits": 21,
        "idxs": [
          17,
          18,
          19
        ]
      }
    ],
    "title": "Details for each phrase/column combination"
  },
  "n": {
    "value": 336,
    "title": "Number of rows in the FTS4 table",
    "idx": 20
  },
  "a": {
    "title": "Average number of tokens in each column across the whole table",
    "value": [
      {
        "column_index": 0,
        "average_num_tokens": 5,
        "idx": 21
      },
      {
        "column_index": 1,
        "average_num_tokens": 2,
        "idx": 22
      },
      {
        "column_index": 2,
        "average_num_tokens": 1653,
        "idx": 23
      }
    ]
  },
  "l": {
    "title": "Number of tokens in each column of the current row of the FTS4 table",
    "value": [
      {
        "column_index": 0,
        "num_tokens": 7,
        "idx": 24
      },
      {
        "column_index": 1,
        "num_tokens": 2,
        "idx": 25
      },
      {
        "column_index": 2,
        "num_tokens": 1987,
        "idx": 26
      }
    ]
  },
  "y": {
    "value": [
      {
        "phrase_index": 0,
        "column_index": 0,
        "hits_for_phrase_in_col": 1,
        "idx": 27
      },
      {
        "phrase_index": 0,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 28
      },
      {
        "phrase_index": 0,
        "column_index": 2,
        "hits_for_phrase_in_col": 19,
        "idx": 29
      },
      {
        "phrase_index": 1,
        "column_index": 0,
        "hits_for_phrase_in_col": 0,
        "idx": 30
      },
      {
        "phrase_index": 1,
        "column_index": 1,
        "hits_for_phrase_in_col": 0,
        "idx": 31
      },
      {
        "phrase_index": 1,
        "column_index": 2,
        "hits_for_phrase_in_col": 2,
        "idx": 32
      }
    ],
    "title": "Usable phrase matches for each phrase/column combination"
  },
  "b": {
    "title": "Bitfield showing which phrases occur in which columns",
    "value": [
      5,
      4
    ],
    "decoded": {
      "phrase_0": "10100000000000000000000000000000",
      "phrase_1": "00100000000000000000000000000000"
    }
  }
}
Powered by Datasette · Query took 2.965ms