1# dialects/mysql/expression.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7from ... import exc
8from ... import util
9from ...sql import coercions
10from ...sql import elements
11from ...sql import operators
12from ...sql import roles
13from ...sql.base import _generative
14from ...sql.base import Generative
15
16
17class match(Generative, elements.BinaryExpression):
18 """Produce a ``MATCH (X, Y) AGAINST ('TEXT')`` clause.
19
20 E.g.::
21
22 from sqlalchemy import desc
23 from sqlalchemy.dialects.mysql import match
24
25 match_expr = match(
26 users_table.c.firstname,
27 users_table.c.lastname,
28 against="Firstname Lastname",
29 )
30
31 stmt = (
32 select(users_table)
33 .where(match_expr.in_boolean_mode())
34 .order_by(desc(match_expr))
35 )
36
37 Would produce SQL resembling::
38
39 SELECT id, firstname, lastname
40 FROM user
41 WHERE MATCH(firstname, lastname) AGAINST (:param_1 IN BOOLEAN MODE)
42 ORDER BY MATCH(firstname, lastname) AGAINST (:param_2) DESC
43
44 The :func:`_mysql.match` function is a standalone version of the
45 :meth:`_sql.ColumnElement.match` method available on all
46 SQL expressions, as when :meth:`_expression.ColumnElement.match` is
47 used, but allows to pass multiple columns
48
49 :param cols: column expressions to match against
50
51 :param against: expression to be compared towards
52
53 :param in_boolean_mode: boolean, set "boolean mode" to true
54
55 :param in_natural_language_mode: boolean , set "natural language" to true
56
57 :param with_query_expansion: boolean, set "query expansion" to true
58
59 .. versionadded:: 1.4.19
60
61 .. seealso::
62
63 :meth:`_expression.ColumnElement.match`
64
65 """
66
67 __visit_name__ = "mysql_match"
68
69 inherit_cache = True
70
71 def __init__(self, *cols, **kw):
72 if not cols:
73 raise exc.ArgumentError("columns are required")
74
75 against = kw.pop("against", None)
76
77 if against is None:
78 raise exc.ArgumentError("against is required")
79 against = coercions.expect(
80 roles.ExpressionElementRole,
81 against,
82 )
83
84 left = elements.BooleanClauseList._construct_raw(
85 operators.comma_op,
86 clauses=cols,
87 )
88 left.group = False
89
90 flags = util.immutabledict(
91 {
92 "mysql_boolean_mode": kw.pop("in_boolean_mode", False),
93 "mysql_natural_language": kw.pop(
94 "in_natural_language_mode", False
95 ),
96 "mysql_query_expansion": kw.pop("with_query_expansion", False),
97 }
98 )
99
100 if kw:
101 raise exc.ArgumentError("unknown arguments: %s" % (", ".join(kw)))
102
103 super(match, self).__init__(
104 left, against, operators.match_op, modifiers=flags
105 )
106
107 @_generative
108 def in_boolean_mode(self):
109 """Apply the "IN BOOLEAN MODE" modifier to the MATCH expression.
110
111 :return: a new :class:`_mysql.match` instance with modifications
112 applied.
113 """
114
115 self.modifiers = self.modifiers.union({"mysql_boolean_mode": True})
116
117 @_generative
118 def in_natural_language_mode(self):
119 """Apply the "IN NATURAL LANGUAGE MODE" modifier to the MATCH
120 expression.
121
122 :return: a new :class:`_mysql.match` instance with modifications
123 applied.
124 """
125
126 self.modifiers = self.modifiers.union({"mysql_natural_language": True})
127
128 @_generative
129 def with_query_expansion(self):
130 """Apply the "WITH QUERY EXPANSION" modifier to the MATCH expression.
131
132 :return: a new :class:`_mysql.match` instance with modifications
133 applied.
134 """
135
136 self.modifiers = self.modifiers.union({"mysql_query_expansion": True})