QGIS API Documentation  3.25.0-Master (6b426f5f8a)
qgssearchquerybuilder.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgssearchquerybuilder.cpp - Query builder for search strings
3  ----------------------
4  begin : March 2006
5  copyright : (C) 2006 by Martin Dobias
6  email : wonder.sk at gmail dot com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 
16 #include <QDomDocument>
17 #include <QDomElement>
18 #include <QFileDialog>
19 #include <QFileInfo>
20 #include <QInputDialog>
21 #include <QListView>
22 #include <QMessageBox>
23 #include <QStandardItem>
24 #include <QTextStream>
25 
26 #include "qgssettings.h"
27 #include "qgsfeature.h"
28 #include "qgsfeatureiterator.h"
29 #include "qgsfields.h"
30 #include "qgssearchquerybuilder.h"
31 #include "qgsexpression.h"
32 #include "qgsvectorlayer.h"
33 #include "qgslogger.h"
34 #include "qgshelp.h"
36 #include "qgsquerybuilder.h"
37 
38 
40  QWidget *parent, Qt::WindowFlags fl )
41  : QDialog( parent, fl )
42  , mLayer( layer )
43 {
44  setupUi( this );
45  connect( btnEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnEqual_clicked );
46  connect( btnLessThan, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLessThan_clicked );
47  connect( btnGreaterThan, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGreaterThan_clicked );
48  connect( btnLike, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLike_clicked );
49  connect( btnILike, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnILike_clicked );
50  connect( btnPct, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnPct_clicked );
51  connect( btnIn, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnIn_clicked );
52  connect( btnNotIn, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNotIn_clicked );
53  connect( lstFields, &QListView::doubleClicked, this, &QgsSearchQueryBuilder::lstFields_doubleClicked );
54  connect( lstValues, &QListView::doubleClicked, this, &QgsSearchQueryBuilder::lstValues_doubleClicked );
55  connect( btnLessEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLessEqual_clicked );
56  connect( btnGreaterEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGreaterEqual_clicked );
57  connect( btnNotEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNotEqual_clicked );
58  connect( btnAnd, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnAnd_clicked );
59  connect( btnNot, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNot_clicked );
60  connect( btnOr, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnOr_clicked );
61  connect( btnGetAllValues, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGetAllValues_clicked );
62  connect( btnSampleValues, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnSampleValues_clicked );
63  setupListViews();
64  connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsSearchQueryBuilder::showHelp );
65 
66  setWindowTitle( tr( "Search Query Builder" ) );
67 
68  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
69  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
70  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::btnTest_clicked );
71 
72  pbn = new QPushButton( tr( "&Clear" ) );
73  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
74  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::btnClear_clicked );
75 
76  pbn = new QPushButton( tr( "&Save…" ) );
77  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
78  pbn->setToolTip( tr( "Save query to an xml file" ) );
79  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::saveQuery );
80 
81  pbn = new QPushButton( tr( "&Load…" ) );
82  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
83  pbn->setToolTip( tr( "Load query from xml file" ) );
84  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::loadQuery );
85 
86  if ( layer )
87  lblDataUri->setText( layer->name() );
88  populateFields();
89 }
90 
91 void QgsSearchQueryBuilder::populateFields()
92 {
93  if ( !mLayer )
94  return;
95 
96  const QgsFields &fields = mLayer->fields();
97  for ( int idx = 0; idx < fields.count(); ++idx )
98  {
99  const QString fieldName = fields.at( idx ).name();
100  mFieldMap[fieldName] = idx;
101  QStandardItem *myItem = new QStandardItem( fieldName );
102  myItem->setEditable( false );
103  mModelFields->insertRow( mModelFields->rowCount(), myItem );
104  }
105 }
106 
107 void QgsSearchQueryBuilder::setupListViews()
108 {
109  //Models
110  mModelFields = new QStandardItemModel();
111  mModelValues = new QStandardItemModel();
112  lstFields->setModel( mModelFields );
113  lstValues->setModel( mModelValues );
114  // Modes
115  lstFields->setViewMode( QListView::ListMode );
116  lstValues->setViewMode( QListView::ListMode );
117  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
118  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
119  // Performance tip since Qt 4.1
120  lstFields->setUniformItemSizes( true );
121  lstValues->setUniformItemSizes( true );
122 }
123 
124 void QgsSearchQueryBuilder::getFieldValues( int limit )
125 {
126  if ( !mLayer )
127  {
128  return;
129  }
130  // clear the values list
131  mModelValues->clear();
132 
133  // determine the field type
134  const QString fieldName = mModelFields->data( lstFields->currentIndex() ).toString();
135  const int fieldIndex = mFieldMap[fieldName];
136  const QgsField field = mLayer->fields().at( fieldIndex );//provider->fields().at( fieldIndex );
137  const bool numeric = ( field.type() == QVariant::Int || field.type() == QVariant::Double );
138 
139  QgsFeature feat;
140  QString value;
141 
142  QgsAttributeList attrs;
143  attrs.append( fieldIndex );
144 
146 
147  lstValues->setCursor( Qt::WaitCursor );
148  // Block for better performance
149  mModelValues->blockSignals( true );
150  lstValues->setUpdatesEnabled( false );
151 
152  // MH: keep already inserted values in a set. Querying is much faster compared to QStandardItemModel::findItems
153  QSet<QString> insertedValues;
154 
155  while ( fit.nextFeature( feat ) &&
156  ( limit == 0 || mModelValues->rowCount() != limit ) )
157  {
158  value = feat.attribute( fieldIndex ).toString();
159 
160  if ( !numeric )
161  {
162  // put string in single quotes and escape single quotes in the string
163  value = '\'' + value.replace( '\'', QLatin1String( "''" ) ) + '\'';
164  }
165 
166  // add item only if it's not there already
167  if ( !insertedValues.contains( value ) )
168  {
169  QStandardItem *myItem = new QStandardItem( value );
170  myItem->setEditable( false );
171  mModelValues->insertRow( mModelValues->rowCount(), myItem );
172  insertedValues.insert( value );
173  }
174  }
175  // Unblock for normal use
176  mModelValues->blockSignals( false );
177  lstValues->setUpdatesEnabled( true );
178  // TODO: already sorted, signal emit to refresh model
179  mModelValues->sort( 0 );
180  lstValues->setCursor( Qt::ArrowCursor );
181 }
182 
183 void QgsSearchQueryBuilder::btnSampleValues_clicked()
184 {
185  getFieldValues( 25 );
186 }
187 
188 void QgsSearchQueryBuilder::btnGetAllValues_clicked()
189 {
190  getFieldValues( 0 );
191 }
192 
193 void QgsSearchQueryBuilder::btnTest_clicked()
194 {
195  const long count = countRecords( mTxtSql->text() );
196 
197  // error?
198  if ( count == -1 )
199  return;
200 
201  QMessageBox::information( this, tr( "Test Query" ), tr( "Found %n matching feature(s).", "test result", count ) );
202 }
203 
204 // This method tests the number of records that would be returned
205 long QgsSearchQueryBuilder::countRecords( const QString &searchString )
206 {
207  QgsExpression search( searchString );
208  if ( search.hasParserError() )
209  {
210  QMessageBox::critical( this, tr( "Query Result" ), search.parserErrorString() );
211  return -1;
212  }
213 
214  if ( !mLayer )
215  return -1;
216 
217  const bool fetchGeom = search.needsGeometry();
218 
219  int count = 0;
220  QgsFeature feat;
221 
223 
224  if ( !search.prepare( &context ) )
225  {
226  QMessageBox::critical( this, tr( "Query Result" ), search.evalErrorString() );
227  return -1;
228  }
229 
230  QApplication::setOverrideCursor( Qt::WaitCursor );
231 
233 
234  while ( fit.nextFeature( feat ) )
235  {
236  context.setFeature( feat );
237  const QVariant value = search.evaluate( &context );
238  if ( value.toInt() != 0 )
239  {
240  count++;
241  }
242 
243  // check if there were errors during evaluating
244  if ( search.hasEvalError() )
245  break;
246  }
247 
248  QApplication::restoreOverrideCursor();
249 
250  if ( search.hasEvalError() )
251  {
252  QMessageBox::critical( this, tr( "Query Result" ), search.evalErrorString() );
253  return -1;
254  }
255 
256  return count;
257 }
258 
259 
260 void QgsSearchQueryBuilder::btnOk_clicked()
261 {
262  // if user hits OK and there is no query, skip the validation
263  if ( mTxtSql->text().trimmed().length() > 0 )
264  {
265  accept();
266  return;
267  }
268 
269  // test the query to see if it will result in a valid layer
270  const long numRecs = countRecords( mTxtSql->text() );
271  if ( numRecs == -1 )
272  {
273  // error shown in countRecords
274  }
275  else if ( numRecs == 0 )
276  {
277  QMessageBox::warning( this, tr( "Query Result" ), tr( "The query you specified results in zero records being returned." ) );
278  }
279  else
280  {
281  accept();
282  }
283 
284 }
285 
286 void QgsSearchQueryBuilder::btnEqual_clicked()
287 {
288  mTxtSql->insertText( QStringLiteral( " = " ) );
289 }
290 
291 void QgsSearchQueryBuilder::btnLessThan_clicked()
292 {
293  mTxtSql->insertText( QStringLiteral( " < " ) );
294 }
295 
296 void QgsSearchQueryBuilder::btnGreaterThan_clicked()
297 {
298  mTxtSql->insertText( QStringLiteral( " > " ) );
299 }
300 
301 void QgsSearchQueryBuilder::btnPct_clicked()
302 {
303  mTxtSql->insertText( QStringLiteral( "%" ) );
304 }
305 
306 void QgsSearchQueryBuilder::btnIn_clicked()
307 {
308  mTxtSql->insertText( QStringLiteral( " IN " ) );
309 }
310 
311 void QgsSearchQueryBuilder::btnNotIn_clicked()
312 {
313  mTxtSql->insertText( QStringLiteral( " NOT IN " ) );
314 }
315 
316 void QgsSearchQueryBuilder::btnLike_clicked()
317 {
318  mTxtSql->insertText( QStringLiteral( " LIKE " ) );
319 }
320 
322 {
323  return mTxtSql->text();
324 }
325 
326 void QgsSearchQueryBuilder::setSearchString( const QString &searchString )
327 {
328  mTxtSql->setText( searchString );
329 }
330 
331 void QgsSearchQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
332 {
333  mTxtSql->insertText( QgsExpression::quotedColumnRef( mModelFields->data( index ).toString() ) );
334 }
335 
336 void QgsSearchQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
337 {
338  mTxtSql->insertText( mModelValues->data( index ).toString() );
339 }
340 
341 void QgsSearchQueryBuilder::btnLessEqual_clicked()
342 {
343  mTxtSql->insertText( QStringLiteral( " <= " ) );
344 }
345 
346 void QgsSearchQueryBuilder::btnGreaterEqual_clicked()
347 {
348  mTxtSql->insertText( QStringLiteral( " >= " ) );
349 }
350 
351 void QgsSearchQueryBuilder::btnNotEqual_clicked()
352 {
353  mTxtSql->insertText( QStringLiteral( " != " ) );
354 }
355 
356 void QgsSearchQueryBuilder::btnAnd_clicked()
357 {
358  mTxtSql->insertText( QStringLiteral( " AND " ) );
359 }
360 
361 void QgsSearchQueryBuilder::btnNot_clicked()
362 {
363  mTxtSql->insertText( QStringLiteral( " NOT " ) );
364 }
365 
366 void QgsSearchQueryBuilder::btnOr_clicked()
367 {
368  mTxtSql->insertText( QStringLiteral( " OR " ) );
369 }
370 
371 void QgsSearchQueryBuilder::btnClear_clicked()
372 {
373  mTxtSql->clear();
374 }
375 
376 void QgsSearchQueryBuilder::btnILike_clicked()
377 {
378  mTxtSql->insertText( QStringLiteral( " ILIKE " ) );
379 }
380 
382 {
383  QgsQueryBuilder::saveQueryToFile( mTxtSql->text() );
384 }
385 
387 {
388  QString query;
389  if ( QgsQueryBuilder::loadQueryFromFile( query ) )
390  {
391  mTxtSql->clear();
392  mTxtSql->insertText( query );
393  }
394 }
395 
396 void QgsSearchQueryBuilder::showHelp()
397 {
398  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
399 }
static QList< QgsExpressionContextScope * > globalProjectLayerScopes(const QgsMapLayer *layer)
Creates a list of three scopes: global, layer's project and layer.
Expression contexts are used to encapsulate the parameters around which a QgsExpression should be eva...
Class for parsing and evaluation of expressions (formerly called "search strings").
static QString quotedColumnRef(QString name)
Returns a quoted column reference (in double quotes)
Wrapper for iterator of features from vector data provider or vector layer.
bool nextFeature(QgsFeature &f)
This class wraps a request for features to a vector layer (or directly its vector data provider).
QgsFeatureRequest & setFlags(QgsFeatureRequest::Flags flags)
Sets flags that affect how features will be fetched.
QgsFeatureRequest & setSubsetOfAttributes(const QgsAttributeList &attrs)
Set a subset of attributes that will be fetched.
@ NoGeometry
Geometry is not required. It may still be returned if e.g. required for a filter condition.
The feature class encapsulates a single feature including its unique ID, geometry and a list of field...
Definition: qgsfeature.h:56
QVariant attribute(const QString &name) const
Lookup attribute value by attribute name.
Definition: qgsfeature.cpp:320
Encapsulate a field in an attribute table or data source.
Definition: qgsfield.h:51
QString name
Definition: qgsfield.h:60
QVariant::Type type
Definition: qgsfield.h:58
Container of fields for a vector layer.
Definition: qgsfields.h:45
int count() const
Returns number of items.
Definition: qgsfields.cpp:133
QgsField at(int i) const
Returns the field at particular index (must be in range 0..N-1).
Definition: qgsfields.cpp:163
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:36
QString name
Definition: qgsmaplayer.h:76
static bool loadQueryFromFile(QString &subset)
Load query from the XML file.
static bool saveQueryToFile(const QString &subset)
Save query to the XML file.
QgsSearchQueryBuilder(QgsVectorLayer *layer, QWidget *parent=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
Constructor - takes pointer to vector layer as a parameter.
void setSearchString(const QString &searchString)
change search string shown in text field
QString searchString()
returns newly created search string
Represents a vector layer which manages a vector based data sets.
QgsFeatureIterator getFeatures(const QgsFeatureRequest &request=QgsFeatureRequest()) const FINAL
Queries the layer for features specified in request.
QgsFields fields() const FINAL
Returns the list of fields of this layer.
QList< int > QgsAttributeList
Definition: qgsfield.h:26
const QgsField & field
Definition: qgsfield.h:463